Doing a '-1' from SELECTEDVALUE dax command

[Anonymous]

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.

[amitchandak]

@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]

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]

@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]

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.

[v-rzhou-msft · Accepted solution]

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]

Thanks @v-rzhou-msft 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 @v-rzhou-msft 

[Anonymous]

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?