Forum Discussion
Calculate with Dynamic Date Filtering
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
Hi amitchandak ,
Field Parameters is exactly what I'm using to make my Date Axis table. The issue I'm having is I want to create one set of measures to calculate % difference over time and would like to pass the Date Axis Field Parameter through it dynamically so the date range can be adjusted by a filter.
Any idea how I could allow the DAX CALCULATE function to change the date range based on the Date Axid Field Parameter?
Mark
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-517aacc23fdfHi 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
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
