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 which shows difference wrt last year i.e current period - last year period. So whenever a user selects a period it will show that years sales and variance wrt last year. 

 

For example if selected period is 'July FY2021' then sales of this period will show in one KPI card and the other will show 'July FY2021 - July FY2020' difference. I have also created a column where month-year are in whole number format so i can do a -1 i.e 072021-1 would be 072020. The measure I tried was

 

CALCULATE(SALES, SELECTEDVALUE(SLICER COLUMN NAME July FY2021) - CALCULATE(SALES, SELECTED(Slicer Column 072021) - 1)

 

Although this isnt helping me out since it says SelectedValues doesnt accept True/False values. Can anyone help? Any alternative solutions are also welcome. P.S Time seriesdax capabilities arent working in this scenario too.

 

 

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.

     

7 Replies

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

    Anonymous , you should use date table and time intelligence

     

    example

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))

     

     

    and then take a diff

     

    use month year from date table

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • 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))