Please explain YTD calculation in this simple example

[rpinxt]

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 on month (date) that come from the x-axis wtih :

YTD = CALCULATE(

    SUM(Sheet1[Qty]),

    REMOVEFILTERS(Sheet1[Date])

)

Also tried with ALL instead of REMOVEFILTERS but result is the same.

I will just show the Mth numbers.

Really don't understand why I always have this much problems with removing/ignoring filters 🤣.

Seems to work different everytime.

[rpinxt · Accepted solution]

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 · Accepted solution]

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]

@amitchandak I tried also in the simple example with datesytd function but thats does not take the type into account :

It should count A 5 + 12 = 17 not 39.

It just counts every Qty without taking in account any other fields.

[rpinxt · Accepted solution]

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]

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??

[amitchandak]

@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-bd52912a5b...
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.

[philouduv]

Hello @rpinxt ,
I would say it's because when you add the date as an abscis it automatically include date hierarchy and so display month, if you go on the Vizalisation panel could you verify ? (If you expand it)