Forum Discussion

udian's avatar
udian
Icon for Helper III rankHelper III
10 years ago
Solved

Dynamic filtering according to other filter selction

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's avatar
    MattAllington
    10 years ago

    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's avatar
    v-haibl-msft
    10 years ago

    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

3 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    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