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 😉
rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
2 years ago

Ok thanks all.

Normally I would use a date table amitchandak but this was just a very small and simple example data that I did not bother.

 

But cannot be that this simple example can not be done in a "normal" way??

 

I will add this link here were this little example is :

https://drive.google.com/file/d/1eIdIk-8pznDjt9Xox9gcJB1oTInqYdSh/view?usp=sharing

 

As you will see I did just some random numbers for different dates.

Should not be rocket sience to make this a simple YTD amount instead of monthly amounts should it??