YTD dynamic table in dax

[Sándor]

Dear All!

I would like to kindly ask for your help.

I have created a simple table with the value of 1 for each day. Just to keep it simple.

I am calculating the YTD value with the following measure.

YTD = CALCULATE('Measures-table'[Total],DATESYTD(Sheet1[Date]))

Can you please help me how can I achieve a dynamic table, where the first row is always Today and value of Today is the YTD amount from the very beginning?

This is the goal I would like to achieve given today's date is 18.05.2025 and the value cumulated from the very beginning is 18.

My issue is, no matter what I tried the value for today got always 1, as it was the first day in the range and not the 18th over all.

Thank you in advance

Best regards,

Sándor

[danextian · Accepted solution]

Hi @Sándor 

If you are trying to exclude future dates from showing up in YTD, you can use double calculate. Also, please start using a separate dates/calendar table that's been marked as such.  Set and use date tables in Power BI Desktop

YTD Value today = 
VAR _MAX =
    TODAY ()
RETURN
    CALCULATE (
        CALCULATE ( SUM ( 'Table'[Value] ), DATESYTD ( 'Calendar'[Date] ) ),
        KEEPFILTERS ( 'Calendar'[Date] <= _MAX )
    )

Please see the attached pbix.

[maruthisp · Accepted solution]

Hi @Sándor ,

I tried to replicate and fix the issue, Please find the attached pbix file for oyur reference.
YTD dynamic table in dax.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[Ashish_Excel]

Hi,

Share the download link of the PBI file.

[danextian · Accepted solution]

Hi @Sándor 

If you are trying to exclude future dates from showing up in YTD, you can use double calculate. Also, please start using a separate dates/calendar table that's been marked as such.  Set and use date tables in Power BI Desktop

YTD Value today = 
VAR _MAX =
    TODAY ()
RETURN
    CALCULATE (
        CALCULATE ( SUM ( 'Table'[Value] ), DATESYTD ( 'Calendar'[Date] ) ),
        KEEPFILTERS ( 'Calendar'[Date] <= _MAX )
    )

Please see the attached pbix.

[Sándor]

Dear @danextian  @Ashish_Excel!

Thank you very much for your help.

It worked well.

In the meanwhile I came up with another solution as well.

@danextian I am using a different data table in my main model. I just wanted to make the explanation simple.

Thank you

Br

S