Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

TOTALYTD Question

Here is my data

CategoryJan-24Feb-24Mar-24Apr-24May-24
Comm 61,742,741 68,152,538 68,794,916 66,809,865 42,966,036
Md 27,267,359 28,160,052 28,267,516 27,198,823 17,155,274
Mc 82,872,387 83,093,551 88,866,556 86,224,604 55,641,799
OG   4,167,172   4,287,791   3,898,294   4,125,696   2,509,877
SP   4,043,743   4,346,606   4,268,509   4,200,256   2,578,727

 

I am trying to calculate the YTD values and expect to see the below.

My formula I used was 

Charges_YTD = TOTALYTD(SUM(Table1[Charge]),Table1[Post Date - Date],

Table1[Post Date - Date] = DATE(2024,5,31))

I even removed the last optional part but I am not getting my expected values

 

What am I missing here ?  Thank you.

 

CategoryJan-24Feb-24Mar-24Apr-24May-24
Comm   61,742,741 129,895,279 198,690,195 265,500,060 308,466,096
Md   27,267,359   55,427,411   83,694,927 110,893,750 128,049,024
Mc   82,872,387 165,965,938 254,832,494 341,057,099 396,698,897
OG     4,167,172     8,454,962   12,353,256   16,478,952   18,988,830
SP     4,043,743     8,390,349   12,658,858   16,859,114   19,437,842

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi Anonymous - Please create a seperate Date table, it works. 

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Please try:

    Charges YTD = 
VAR LastMonthAvailable = MAX ( 'Calendar'[Month-Year-Sort] )
VAR LastYearAvailable = MAX ( 'Calendar'[Year] )
VAR Category = MAX( 'Table1'[Category] )
VAR Result =
    CALCULATE (
        [Total Charges],
        REMOVEFILTERS ( 'Calendar' ),
        'Calendar'[Month-Year-Sort] <= LastMonthAvailable,
        'Calendar'[Year] = LastYearAvailable,
        VALUES('Table1'[Category])
    )
RETURN
    Result

    or:

    Charges YTD 2 = CALCULATE([Total Charges],DATESYTD('Calendar'[Date]),VALUES('Table1'[Category]))

    or:

    Charges YTD 3 = TOTALYTD([Total Charges],'Calendar'[Date],VALUES(Table1[Category]))

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

8 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Anonymous - I hope you already have the seperate date table and marked it as date, if not please create it.

    create a quick date table using the date in your YTD Measure.

    Date = CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31))

     

    YTD Measure:

    Charges_YTD =
    TOTALYTD(
    SUM('Table1'[Charge]),
    'Date'[Date]
    )

     

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Aah.  So I have to have the Date column in a separate calendar table and it cannot be part of the other data is that the reason ?  Should the Date be always separate ?

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        2 years ago

        Hi Anonymous - Please create a seperate Date table, it works. 

         

        Did I answer your question? Mark my post as a solution! This will help others on the forum!
        Appreciate your Kudos!!