Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Show variance based on filter selection

Hello experts!

 

I have created some measures to display the variance between a week and the previous one to that however I would like to be able to have the variance showing accordingly to a slicer selection. If nothing is selected then show the difference with previous week as I already have it:

YearQuarterMonthDayWEEKFCSTPREV. WK FCSTFCST DIFF vs PREV. WK
2020Qtr 1January62020021,861,9801,864,549-2,569
2019Qtr 4December302020011,864,5491,904,024-39,475
2019Qtr 4December232019521,904,0241,895,5398,485
2019Qtr 4December162019511,895,5391,868,80226,737
2019Qtr 4December92019501,868,8021,855,45713,345
2019Qtr 4December22019491,855,457  

 

FCST = SUM(FCST[FCST QTY])

PREV. WK FCST = CALCULATE([FCST],DATEADD('CALENDAR'[Date],-7,DAY))

FCST DIFF vs PREV. WK = [FCST]-[PREV. WK FCST]
 
So, for example, if I pick week 49 and week 52 it should give me the following:
YearQuarterMonthDayWEEKFCSTPREV. WK FCSTFCST DIFF vs PREV. WK
2019Qtr 4December232019521,904,0241,855,45748,567
2019Qtr 4December22019491,855,457  

 

I hope my explanation is clear enough otherwise please, let me know and I will do my best to help you so you can help me 🙂

 

Regards,

 

Anonymous hello Cheenu, I have seen some posts with similar issues so I thought I might contact you directly with mine and see whether you can help me 🙂 

Thanks!!

Need Help

4 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    Anonymous ,

     

    Modify the measure using dax below:

    PREV. WK FCST =
CALCULATE (
    [FCST],
    FILTER ( ALLSELECTED ( Table ), DATEADD ( 'CALENDAR'[Date], -7, DAY ) )
)

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello v-yuta-msft 

       

      For some reason it doesn't work. I am not sure whether I am doing it properly or not but just to be sure, to which 'table' should I refer the ALLSELECTED function? I tried with my calendar table and didn't work.

       

      Thanks in advance!

      Regards,

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        6 years ago

        Anonymous ,

         

        This based on your slicer. If you build slicer based on date column in the fact table, then the expression should be ALLSELECTED(Fact_Table). 

         

        Community Support Team _ Jimmy Tao

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.