Forum Discussion

rpinxt's avatar
rpinxt
Solution 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 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.
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 😉

6 Replies

  • amitchandak's avatar
    amitchandak
    Super 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.

  • philouduv's avatar
    philouduv
    Resolver III
    2 years ago

    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)

  • rpinxt's avatar
    rpinxt
    Solution 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??

  • rpinxt's avatar
    rpinxt
    Solution Sage
    2 years ago

    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's avatar
      rpinxt
      Solution Sage
      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
    Solution Sage
    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 😉