Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi team,
How can i create a measure which shows % variance of sales of current year and previous year.
the variance should work with below slicer like Today,MTD,YTD and also with date slicer like Year,Month and Day.
below vPY is the variance in sales.I want this measure to be filtered by all the slicers.
tagging for help @Jihwan_Kim @amitchandak
Thanks
nj
Hi @nj17
Typically the solution would look like:
PY Delta % =
var var_CY_value = [your base measure]
var var_PY_Value=
CALCULATE(
[your base measure],
DATEADD(Date_Table[Date Column],-1,MONTH)
)
RETURN
DIVIDE(var_CY_value,var_PY_Value) - 1
Now it depends on your base measure as well as on your data model whether and how it works with the slicers and settings you mentioned.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Please find the link where i uploaded my file
https://www.dropbox.com/s/2lz9mbcdyq67rno/test.pbix?dl=0
When I select Year from slicer ,values are filtering
when I select below filter from mtd ytd slicer then no value appear for both the measures
thanks
nj
Hi @nj17,
Due to security reasons in general I dont download files. I hope for your understanding.
Can you please provide a picture of the data model as well as the current measure definitions you use? Then it should be quickly solvable.
Best regards
Michael
Hi @Mikelytics
this is the calendar table i created
Slicer I created for MTD,YTD and Daily
Data model
Measures I created
User | Count |
---|---|
122 | |
97 | |
89 | |
76 | |
68 |
User | Count |
---|---|
139 | |
115 | |
114 | |
98 | |
98 |