Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

TOTALYTD Question

Here is my data Category Jan-24 Feb-24 Mar-24 Apr-24 May-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...
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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        It looks like works.  But I now have a different issue.

        Attaching 2 pictures

        Its not visible - but I have joined on Post Date - Date and DateKey

         

         

        If I track Charges YTD as highlighted here - the numbers seem right as it builds on the previous date.

        BUT - I just want to see Months not each day.  Creating a date hierachy for that column is not getting me the right number.

        I tried to right click to see if Collapse option was there and nothing there.

         

        What am I missing here ?  Thanks.