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. ...
Need Help
Tips and Tricks
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?