Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Guys
I'm still new with Power BI Desktop and I have one senario where I have current and previous years which user should select. and based on that the calculation should be substraction of values.
example:
Value | DATE |
200 | 28-Dec-22 |
345 | 05-Dec-23 |
500 | 29-Dec-18 |
980 | 13-Dec-19 |
441 | 22-Dec-20 |
If user select previous year as (28-Dec-22) and current year (05-Dec-23) the calculation will be (value=345-200)
could any one please help!
Can anyone please help!
Hello @AsNa_92,
Can you please try the following steps:
1. First, ensure you have a Date table that covers the range of dates in your data. Next, create Slicers for Year Selection.
Year = YEAR(Date[Date])
2. Create Measures for Selected Years
Previous Year Value =
VAR SelectedPreviousYear = SELECTEDVALUE(Date[Year])
RETURN
CALCULATE(SUM(Table[Value]), FILTER(ALL(Table), YEAR(Table[DATE]) = SelectedPreviousYear))
Current Year Value =
VAR SelectedCurrentYear = SELECTEDVALUE(Date[Year], MAX(Date[Year]))
RETURN
CALCULATE(SUM(Table[Value]), FILTER(ALL(Table), YEAR(Table[DATE]) = SelectedCurrentYear))
3. Create a Measure for Subtraction
Value Difference =
[Current Year Value] - [Previous Year Value]
Hi Sahir_Maharaj,
Thank for replying, I didn't get the Year=YEAR(Date[Date])? So, Icannot use the Date column I already have? or do I create a new column for Year? and when I try to create the Year measure it shows error like what is exactly the (Date[Date])?
Also, the current and previous years measure cannot be inserted into a slicer. The logic is the user will select from a slicers a current year and Previous year same as below:
The YTD movment will have the calculation:
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |