Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate with Dynamic Date Filtering

Hi,   I have the following calculated table in my data, built using the parameter function in the modeling section: 02c: Switch - Date Axis = { ("Date", NAMEOF('02a: Calendar'[Dates]), 0, NAM...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi,

     

    I figured it out:

     

    var previousvalue = SWITCH(SELECTEDVALUE('02c: Switch - Date Axis'[Fields - Date Axis]),
NAMEOF('02a: Calendar'[Dates]),CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[Date ID] = SELECTEDVALUE('02a: Calendar'[Date ID])-1)),
NAMEOF('02a: Calendar'[Start of Week]), CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[Start of Week ID] = SELECTEDVALUE('02a: Calendar'[Start of Week ID])-1)),
NAMEOF('02a: Calendar'[End of Week]), CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[End of Week ID] = SELECTEDVALUE('02a: Calendar'[End of Week ID])-1)),
NAMEOF('02a: Calendar'[MM/YY]), CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[Month Year ID] = SELECTEDVALUE('02a: Calendar'[Month Year ID])-1)),
NAMEOF('02a: Calendar'[QoY/YY]),CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[QoY/YY ID] = SELECTEDVALUE('02a: Calendar'[QoY/YY ID])-1)),
NAMEOF('02a: Calendar'[Year]),CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[Year ID] = SELECTEDVALUE('02a: Calendar'[Year ID])-1)))

RETURN
DIVIDE([CPM]-previousvalue,previousvalue,BLANK())

     

    As my filter only has 6 options, I've used a Switch to move between them and select the date IDs to compare.

     

    Thanks,

    Mark

amitchandak's avatar
amitchandak
Super User
3 years ago

Anonymous , Assume that date rage is not beyond the range filtered for the visual.

 

I can measures like

 

MTD Today =
var _min = eomonth(Maxx(allselected(Date), Date[Date]),-1)+1
var _max = (Maxx(allselected(Date), Date[Date])
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

 

QTD Today =
var _max = Maxx(allselected(Date), Date[Date])
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))+1
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

Now I can switch these measures based on the selected axis. but selectedvalue will work order column not on name column

 

refer how to get selected axis

 

Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
Field Parameters- Conditional Formatting
: https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi amitchandak ,

 

I'm not sure you're quite following what I'm looking for. Let me show you what I want.

 

Here's a screenshot of how this looks on my PBIX file:

 

 

 

 

 

 

 

 

 

 

 

 

 

The filter is created used a Field Parameter. It's got a list of 6 Date options. The example shows it showing Date. With the measure being CPM. I'm trying to get the "Previous Period - CPM" value to act dynamically based on this filter. 

 

Below is a calculation I've made:

Previous Period - CPM = CALCULATE([CPM],FILTER(ALL('02a: Calendar'),
    IF(SELECTEDVALUE('02c: Switch - Date Axis'[ID]) = "0", '02a: Calendar'[Date ID] = max('02a: Calendar'[Date ID])-1,
    IF(SELECTEDVALUE('02c: Switch - Date Axis'[ID]) = "1", '02a: Calendar'[Start of Week ID] = max('02a: Calendar'[Start of Week ID])-1,
    IF(SELECTEDVALUE('02c: Switch - Date Axis'[ID]) = "2", '02a: Calendar'[End of Week ID] = max('02a: Calendar'[End of Week ID])-1,
    IF(SELECTEDVALUE('02c: Switch - Date Axis'[ID]) = "3", '02a: Calendar'[Month Year ID] = MAX('02a: Calendar'[Month Year ID])-1,
    IF(SELECTEDVALUE('02c: Switch - Date Axis'[ID]) = "4", '02a: Calendar'[QoY/YY ID] = MAX('02a: Calendar'[QoY/YY ID])-1,
    IF(SELECTEDVALUE('02c: Switch - Date Axis'[ID]) = "5", '02a: Calendar'[Year ID] = MAX('02a: Calendar'[Year ID])-1,
        '02a: Calendar'[Date ID] = max('02a: Calendar'[Date ID])-1))))))))

In this example, I'm trying to get the calculation to work out the previous MAX ID for each date range so it can then work out what the previous value is, dynamically.

 

How can I set up this one measure so it calculates the value for the previous period depending on the filter/field parameter selection?

 

Does that help?

 

Thanks,

Mark

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,

     

    I figured it out:

     

    var previousvalue = SWITCH(SELECTEDVALUE('02c: Switch - Date Axis'[Fields - Date Axis]),
NAMEOF('02a: Calendar'[Dates]),CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[Date ID] = SELECTEDVALUE('02a: Calendar'[Date ID])-1)),
NAMEOF('02a: Calendar'[Start of Week]), CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[Start of Week ID] = SELECTEDVALUE('02a: Calendar'[Start of Week ID])-1)),
NAMEOF('02a: Calendar'[End of Week]), CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[End of Week ID] = SELECTEDVALUE('02a: Calendar'[End of Week ID])-1)),
NAMEOF('02a: Calendar'[MM/YY]), CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[Month Year ID] = SELECTEDVALUE('02a: Calendar'[Month Year ID])-1)),
NAMEOF('02a: Calendar'[QoY/YY]),CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[QoY/YY ID] = SELECTEDVALUE('02a: Calendar'[QoY/YY ID])-1)),
NAMEOF('02a: Calendar'[Year]),CALCULATE([CPM],FILTER(all('02a: Calendar'),'02a: Calendar'[Year ID] = SELECTEDVALUE('02a: Calendar'[Year ID])-1)))

RETURN
DIVIDE([CPM]-previousvalue,previousvalue,BLANK())

     

    As my filter only has 6 options, I've used a Switch to move between them and select the date IDs to compare.

     

    Thanks,

    Mark