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

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Here is my formula

      ChargesYTD = TOTALYTD(SUM(Table_2024[Charge]),Calendar[DateKey])
       
      If I choose MonthName from the Calendar table, I get no data.  No charges pop up.
      • Anonymous's avatar
        Anonymous
        Not applicable
        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you.  It worked for each day calculation.  Not monthly.