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.

     

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

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