Forum Discussion

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
2 years ago
Solved

Please explain YTD calculation in this simple example

I would think this would work but for some reason it does not. Pretty simple and straight forward : Above the normal month to date. Below I wanted to make year to date by removing the filter...
Need Help
  • rpinxt's avatar
    rpinxt
    2 years ago

    Ok forget about this one I found out how to ammend.
    Apparently with this function it does not take into account filter context so you need to make it yourself 🤨

     

    YTD = CALCULATE(
        SUM(Sheet1[Qty]),
        FILTERS(Sheet1[Type]),
        DATESYTD(dimDate[Date])
    )
     
    Did the trick
  • rpinxt's avatar
    rpinxt
    2 years ago

    Okay after lots of trial and error I found out how to do it also without datesytd function.

    Really don't understand why every case is again lots of trial and error for me 🤣

    Then I need to use ALL, other time I need to use REMOVEFILTER other time it is something else again.

    But Ok I now found the way with datesytd (YTD) and one wihtout (YTD2):

    The dax for the datesytd I already shared. And YTD2 I was able to find by :

    YTD2 = CALCULATE(
        SUM(Sheet1[Qty]),
        Sheet1[Date] <= MAX(Sheet1[Date]),
        FILTERS(Sheet1[Type])
        )
     
    So yes amitchandak thanks for putting me on my way with datesytd.
    I guess by playing around with that one I also found the other way 😉
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

rpinxt , Better to join date with date table and create measure using datesytd

 

YTD = CALCULATE(
SUM(Sheet1[Qty]),datesytd(Date[Date]) )

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.