Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 , 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.
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.
@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))
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.
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.
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |