Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

YTD

I have put this dax together.   YTD AC = TOTALYTD( sumx(FILTER('Append','Append'[view]="actuals"),[Value]), 'FinDate'[Date], 'FinDate'[Date]<TODAY())   I am getting the same result as    ...
ytd dynamic
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    YTD AC = 
CALCULATE (
    SUM ( 'Append'[Value] ),
    FILTER (
        'Append',
        'Append'[view] = "actuals"
            && YEAR ( 'Append'[Date] ) = YEAR ( TODAY () )
            && 'Append'[Date] <= TODAY ()
    )
)

    Best Regards

Poojara_D12's avatar
Poojara_D12
Icon for Super User rankSuper User
1 year ago

Hi Anonymous 

The issue arises because the TOTALYTD function isn't dynamically filtering the dates based on the year-to-date (YTD) range due to the way you structured your DAX formula. Specifically, the FILTER function within SUMX evaluates the entire table, effectively ignoring the TOTALYTD filter context.

Here’s what is missing and how to fix it:

Revised DAX for Dynamic YTD Calculation

You need to make sure that the filtering for YTD is applied correctly. Replace your measure with the following:

 

YTD AC = 
CALCULATE(
    SUMX(
        'Append',
        IF('Append'[view] = "actuals", [Value], 0)
    ),
    DATESYTD('FinDate'[Date], "31/12")
)

 

Testing the Measure

  • Verify that your date column ('FinDate'[Date]) contains valid and continuous date values (i.e., no gaps or missing dates).
  • Ensure that the column 'Append'[view] accurately classifies rows as "actuals" or other categories.

Optional: Debugging Helper Measure

To see which rows are contributing to your calculation, you can create a debugging table:

 

EVALUATE
CALCULATETABLE(
    FILTER('Append', 'Append'[view] = "actuals"),
    DATESYTD('FinDate'[Date], "31/12")
)

 

This will show the filtered rows for YTD, helping you verify that the filters are applied correctly.

Let me know if you encounter any issues!

 

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS