Forum Discussion

hulk_deka's avatar
hulk_deka
Frequent Visitor
4 years ago

CALCULATE YOY WITHOUT LAST YEAR INFO

HELLO EVERZONE,

I am having a tough time finding a solution for my problem.

when i want t o visualise YOY in a waterfall chart it only shows up a value if there are products that have been brought for two consecutive years are available and hence also reflects the same on the total. i am attaching my DAX FORMULA and visualisations below for reference , where you can see the no of products brought each zear and also the YOY for the average below. could someone help me out with the formula or some tips and tricks to overcome this barrier.

 

P.S. Data type for the Year column is taken as WHOLE NUMBER and not as DATE.

proper refelection on waterfall chart when we have consecutive valuesthe situation with a few missing values in between

Need Help
Tips and Tricks

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    hulk_deka , if always better to a year in a separate table like date or year

     

    then you can have measures like

     

    This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])