Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Doing a '-1' from SELECTEDVALUE dax command

Hi all,   My dashboard has a slicer which is like 'July FY2021', so whenever a user selects a particular Month-Year he will see the sales of that particular year month. There is additional KPI whic...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    Try this measure:

    VS LY =
VAR _Selectvalue =
    SELECTEDVALUE ( 'Sample'[Month - Year] )
VAR _CurSales =
    SUM ( 'Sample'[Sales] )
VAR _LYSales =
    SUMX (
        FILTER ( ALL ( 'Sample' ), 'Sample'[Month - Year] = _Selectvalue - 1 ),
        'Sample'[Sales]
    )
RETURN
    _CurSales - _LYSales

    Result is as below.

    Best Regards,

    Rico Zhou

     

    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
5 years ago

This is the sample data for which I am trying to create the measure, the vs LY measure is the part where I am getting stuck. Current year measure is working fine.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

Try this measure:

VS LY =
VAR _Selectvalue =
    SELECTEDVALUE ( 'Sample'[Month - Year] )
VAR _CurSales =
    SUM ( 'Sample'[Sales] )
VAR _LYSales =
    SUMX (
        FILTER ( ALL ( 'Sample' ), 'Sample'[Month - Year] = _Selectvalue - 1 ),
        'Sample'[Sales]
    )
RETURN
    _CurSales - _LYSales

Result is as below.

Best Regards,

Rico Zhou

 

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
    5 years ago

    Thanks Anonymous for the solution, this worked perfectly! One more doubt if you can help, how can I accomodate a already defined measure in this query? Do I just replace sales with the measure? Help would be appreciated Anonymous 

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I've got a similar problem. However, i've done the same function as mentioned but, in my case, if the selectedvalue (main month) has no sales information, even if the selectedvalue - 1 has sales information it will return 0 for both. I've tried many different formulas but the result is only right when I put the month number in the calculate formula.

      Does anyone knows how to fix it?