Forum Discussion

Sándor's avatar
Sándor
New Member
1 year ago
Solved

YTD dynamic table in dax

 

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

 

 

 

Need Help

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      Sándor
      New Member
      1 year ago

      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

  • maruthisp's avatar
    maruthisp
    Icon for Super User rankSuper User
    1 year ago

    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