Dynamic YTD Formula (DAX)

[keckraguilar]

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. 

[Ashish_Mathur]

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.

[keckraguilar]

How do I share an excel doc on here?

[Ashish_Mathur]

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

[keckraguilar]

Here you go. The google doc has additional fields. and yes FY months are July-June

https://docs.google.com/spreadsheets/d/1ACn_70cU0GS1_FO-FAt8LFdDLfbz4TsWfYyYOAWpUJM/edit?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Ashish_Mathur]

There should also be an year column in there.  I cannot take the year from FY2024 because this FY will also include months from the previous CY.  So have a year column as well.

[keckraguilar]

I think that is fine because the budget tab will always only have one Fiscal Year and no previous years

[Ashish_Mathur]

That isn't the point.  I will need that column in order to create a Date field.  This in turn will allow me to create a Calendar table.

[keckraguilar]

OK, but the sales tab is an aggregate of the month. Also, the budget tab is the budget for the month because there is no daily budget. I suppose I can create a date field with the same day of the month if you believe that will work?

[Ashish_Mathur]

Do not bother about creating a Date column - i will create that.  I just need 2 columns in each table - Year and Month name.

[keckraguilar]

OK I just added the year column. Month was already in the file

[danextian]

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]

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?