Forum Discussion

keckraguilar's avatar
keckraguilar
Frequent Visitor
2 years ago
Solved

Dynamic YTD Formula (DAX)

 

I have two tables, budget and sales. I want to create a dynamic ytd budget formula that only sums the data through the max month in the sales table. I already converted the month name to numbers. (not shown) Basically the current snapshot would sum the budget through February, and when I load the data for the next month (march) the formula will automatically update to sum the budget through march. 

 

 

 

 

Need Help
Tips and Tricks

13 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi keckraguilar 

    Please try this:

    Dynamic Budget YTD =
VAR MaxMonthInSales =
    CALCULATE ( MAX ( sales[month number] ), ALL ( sales ) )
RETURN
    CALCULATE (
        SUM ( budget[amount] ),
        FILTER ( ALL ( budget ), budget[month] <= MaxMonthInSales )
    )
    • keckraguilar's avatar
      keckraguilar
      Frequent Visitor
      2 years ago

      thanks for the resonse! the formula doesn't seem to be working. it is showing an inlfated number, not sure if it is still summing all months in the budget file?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Why is there no Year column?  Is the FY from July - June?  Share data in a format that can be pasted in an MS Excel file.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        2 years ago

        Upload it to Google Docs and share the download link.  Please also answer the questions asked in my earlier message.