Calculate with Dynamic Date Filtering

[markhollandau]

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, NAMEOF('02a: Calendar'[Date ID])),
    ("Start of Week DD/MM", NAMEOF('02a: Calendar'[Start of Week DD/MM]), 1, NAMEOF('02a: Calendar'[Week ID])),
    ("End of Week DD/MM", NAMEOF('02a: Calendar'[End of Week DD/MM]), 2, NAMEOF('02a: Calendar'[Week ID])),
    ("Month/Year", NAMEOF('02a: Calendar'[MM/YY]), 3, NAMEOF('02a: Calendar'[Month Year ID])),
    ("Quarter/Year", NAMEOF('02a: Calendar'[QoY/YY]), 4, NAMEOF('02a: Calendar'[QoY/YY ID])),
    ("Year", NAMEOF('02a: Calendar'[Year]), 5, NAMEOF('02a: Calendar'[Year ID]))
}

This is how the table looks:

This allows me to change the axis on my charts between different date ranges - adding the "Field" column to the axis and "Slicer" column in the filter.

I would like to create a CALCULATE function similar to this:

Previous Period - CPM = CALCULATE([CPM],PREVIOUSMONTH('02a: Calendar'[Dates]))

But I would like to use the calculated table to make the filtering a bit more dynamic. For example, if the user selects Start of Week, the CALCULATE function switches to reference that field - and ID from the calculated table - to then work out the previous Start of Week.

I can't use the standard Calendar functions as my Calendars can start on any month of the year.

Can anyone tell me how to achieve this?

Thanks,

Mark

[markhollandau]

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

[amitchandak]

@markhollandau , 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

[markhollandau]

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

[markhollandau · Accepted solution]

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]

@markhollandau , not very clear. But field parameters can help

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
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