Forum Discussion

AsNa_92's avatar
AsNa_92
Resolver II
2 years ago

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 values.

example:

ValueDATE
20028-Dec-22
34505-Dec-23
50029-Dec-18
98013-Dec-19
44122-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!

Need Help

4 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    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]
    • AsNa_92's avatar
      AsNa_92
      Resolver II
      2 years ago

       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: