topic Re: YTD in DAX Commands and Tips

YTD

Anonymous — Mon, 18 Nov 2024 10:28:07 GMT

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

sumx(FILTER('Append','Append'[view]="actuals"),[Value])

Please can you help with what is missing so that this is dynamic and doesn't need filtering every month?

Many thanks,

Re: YTD

Poojara_D12 — Mon, 18 Nov 2024 10:55:01 GMT

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

Re: YTD

Kedar_Pande — Mon, 18 Nov 2024 12:54:19 GMT

Anonymous

Corrected DAX Measure:
YTD AC =
CALCULATE(
SUM('Append'[Value]), 
'Append'[view] = "actuals", 
DATESYTD('FinDate'[Date])
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

Re: YTD

Anonymous — Mon, 18 Nov 2024 17:21:28 GMT

Sorry I tried this, but I still get the whole year, rather than until the current month.

Re: YTD

Anonymous — Mon, 18 Nov 2024 17:21:41 GMT

Sorry I tried this, but I still get the whole year, rather than until the current month.

Re: YTD

Dangar332 — Mon, 18 Nov 2024 17:48:44 GMT

Hi, Anonymous

try below measure

MEASURE = CALCULATE ( [value], 'Append'[view] = "actuals", keepfilter ( 'FinDate'[Date] < TODAY () ) )

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: YTD

Anonymous — Tue, 19 Nov 2024 02:56:45 GMT

Hi Anonymous ,

Please update the formula of measure as below and check if it can return the expected result...

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

Best Regards

Re: YTD

Laxmanjatoth — Tue, 19 Nov 2024 12:18:40 GMT

Anonymous , use the below measure

the reason why iam choosing this measure , if it is more 1 filters then you have to use datesytd function forperfamnace . please check the below one
YTD =
CALCULATE(
SUM('Append'[Value]),
'Append'[view] = "actuals",
DATESYTD('FinDate'[Date])
)
or use below one
YTD AC =
CALCULATE(
SUM('Append'[Value]),
'Append'[view] = "actuals",
DATESYTD('FinDate'[Date])
)

please accept if my measure will works

Re: YTD

Anonymous — Tue, 19 Nov 2024 16:34:29 GMT

Sorry, let me clarify.

My table has dates in the future. That's why I keep getting the values in the future. I realised that I need a measure that calculated the YTD to a point in time.

Re: YTD

Anonymous — Wed, 20 Nov 2024 06:46:25 GMT

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

Re: YTD

Mahesh0016 — Thu, 21 Nov 2024 06:56:24 GMT

Anonymous Please try below one

YTD AC =
CALCULATE(

TOTALYTD( [Total View], 'FinDate'[Date], 'FinDate'[Date]<TODAY() )

, 'Append'[view]="actuals"

)

Anonymous Thank You!