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 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:
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 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |