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

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.  I tried the second option and it worked.