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 slice...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity 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
      Icon for Community Support rankCommunity 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        v-yuta-msft , unfortunately it's not working. 

        So, the measure is working but it's not doing what it's supposed to. 

         

        Slicer is based on a column in my CALENDAR table which is, of course, connected to my fact table.

         

        First 3 colums are my current measures, last one the one using your solution 

         
        PREV. WK FCST =
        CALCULATE ( [FCST], FILTER ( ALLSELECTED ( 'CALENDAR' ), DATEADD('CALENDAR'[Date], -7,DAY) ))
         
        SPI WEEKFCSTPREV. WK FCSTPREV. WK FCST V2
        2020031,868,5861,861,9805,628,067
        2019521,904,0241,895,5395,628,067
        2019491,855,4571,836,3935,628,067

         

        So, I selected 3 different WKS as you can see in table above where the first 3 columns are my current measures and the last one the one you have proposed. What it's actually doing is a SUM of all the selected weeks FCST .

         

        Instead, I would like it to show as follows:

        SPI WEEKFCSTPREV. WK FCSTPREV. WK FCST V2
        2020031,868,5861,861,9801,904,024
        2019521,904,0241,895,5391,855,457
        2019491,855,4571,836,393BLANK

         

        Does it makes sense?

         

        Thanks a lot for your time buddy.

         

        Regards,