Forum Discussion
Calculation based on Years selected
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 va...
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:
