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

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