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

Thanks amitchandak for the prompt response but as mentioned the time intelligence functionality is not working. I have to have a value of -1 year basis the slicer value selected by the user.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous ,

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Check these

 

if you do not have date and you have month year in this format . - 072021

 

You can create date

date = date(right([Month year],4), left([Month year],2),1)

 

Or have it in this format YYYYMM

Year Month = right([Month year],4) & left([Month year],2)

 

have separate year and month too. All in separate table say Date

 

then have rank column if needed

 

Month Rank = RANKX(all('Date'),'Date'[Year Month],,ASC,Dense)  //in separate table

 

Measures 

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

 

Last Year same month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month]=max('Period'[Month]) && 'Date'[Year]=max('Date'[Year])-1))

  • 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