Dynamic filtering according to other filter selction

[udian]

Hi All,

I am building a report that needs to compare two time periods - either current month Vs previous month or previous month Vs two months ago.

I added to indication columns to my time dimesion - Is_Current_Month and Is_Previous_Month.

I also added a new table with two rows - one row for each of the possible comparison scenarions mentioned above (i added the scenario field as a page filter).

I want to filter my reports measure with one of the indication columns in the time table according to the user selction in the comparison scenarios filter.

I tried the following DAX for one of the measures but i get an erroe message saying that i can't use more than one column in the IF formula.

# Partners = CALCULATE(DISTINCTCOUNT(Volume[Partner_ID]),
                     IF(Scenarios[Scenario_ID]=1, TimeTable[Is_Current_Month], TimeTable[Is_Previous_Month]) = 1)

Any ideas as to how can i achive this?

Thanks in advance!!!

[MattAllington · Accepted solution]

I don't think this is the best way to solve this problem. "Time shifting" or time intelligence is a standard feature of Power BI. You need a calendar table that follows "the rules". Read about calendar tables here. 

http://exceleratorbi.com.au/power-pivot-calendar-tables/

Once you you have a valid calendar table, you can use functions like PREVIOUSMONTH 

https://msdn.microsoft.com/en-us/library/ee634758.aspx

Advantages include 

you don't need a column for every different time shift you need

your time shift can compare Feb with Jan and not just current month with previous month as of "today"

[v-haibl-msft · Accepted solution]

@udian

I agree with MattAllington, we can use some time intelligence functions like DATEADD as below.

LastMonth_Sales =

CALCULATE ( SUM ( Table1[Sales] ), DATEADD ( 'Calendar'[Date], -1, MONTH ) )

I’ve uploaded a .pbix file of simple sample here for reference. Hope it helps.

Best Regards,

Herbert

[udian]

Thanks for your help!

[MattAllington · Accepted solution]

I don't think this is the best way to solve this problem. "Time shifting" or time intelligence is a standard feature of Power BI. You need a calendar table that follows "the rules". Read about calendar tables here. 

http://exceleratorbi.com.au/power-pivot-calendar-tables/

Once you you have a valid calendar table, you can use functions like PREVIOUSMONTH 

https://msdn.microsoft.com/en-us/library/ee634758.aspx

Advantages include 

you don't need a column for every different time shift you need

your time shift can compare Feb with Jan and not just current month with previous month as of "today"