Two issues I am currently facing with some financial data
YTD Actual number is at zero when I have no input for the following month. I want the YTD to continue forward in line with my year which runs from January to December. So I have a YTD for September 2023 and I would like that value to carry forward into Oct-23, Nov-23 and Dec-23.
Full Year Budget I can complete by putting in a date of “Dec-23”. However, I would like it more dynamic so that that by just having the month in place it would automatically take that full year for the budget. This would stop me having to change the date each time I start a new year. Also would be more dynamic.
Any assistance with the correct DAX expression would be appreciated.
While we wait for other members to share their insights, we’ve taken the liberty of using “Explain Simply ,” one of the tools available within Data Mentor (you can explore it here: https://mentor.enterprisedna.co/explain-simply . It generated the following results:
Feel free to checkout more of our Data Mentor features as you work on your report. These tools are designed to help with tasks like the one you’re working on.
Carry Forward YTD Actuals
To ensure the YTD Actual continues into future months even when no new values exist:
YTD Actual Carry Forward =
VAR LastNonBlankValue =
CALCULATE(
MAX(YourTable[YTD Actual]),
FILTER(
ALL(YourTable),
YourTable[Date] <= MAX(YourTable[Date]) && NOT(ISBLANK(YourTable[YTD Actual]))
)
)
RETURN
IF(ISBLANK(MAX(YourTable[YTD Actual])), LastNonBlankValue, MAX(YourTable[YTD Actual]))
This ensures that the last available YTD value carries forward into months where no new data is recorded.
Uses ALL(YourTable) to look at all past values instead of just the current filter context.
Dynamic Full-Year Budget Allocation
To avoid manually setting Dec-23, dynamically calculate the full-year budget:
Full Year Budget =
CALCULATE(
SUM(YourTable[Budget]),
ALL(YourTable[Date])
)
Removes date filters, ensuring the full-year budget is always considered.
3. Time Intelligence Enhancements
To analyze trends over time, add Year-to-Date (YTD), Month-over-Month (MoM), and Year-over-Year (YoY) comparisons:
YTD Budget Calculation
YTD Budget =
TOTALYTD( SUM(YourTable[Budget]), DateTable[Date], “12/31” )
Ensures YTD aggregates dynamically based on fiscal year end (December 31 in this case).
Previous Month YTD Actual
YTD Last Month =
CALCULATE( [YTD Actual Carry Forward], PREVIOUSMONTH(DateTable[Date]) )
Helps track YTD progress month-over-month.
Year-over-Year Comparison for YTD
YTD YoY Change =
DIVIDE( [YTD Actual Carry Forward] - CALCULATE( [YTD Actual Carry Forward], SAMEPERIODLASTYEAR(DateTable[Date]) ),
CALCULATE( [YTD Actual Carry Forward], SAMEPERIODLASTYEAR(DateTable[Date]) ),
0 )
Measures YTD growth vs. the same period last year.
I’ve tried the formula suggested but it does not work as required.
For 2022 it should take the YTD based upon month and year so I’ll need to put in place a switch formula.
Below would be the criteria
If month 01 (January) is blank then YTD needs to be blank.
If a value is in the Trial Balance it needs to pick up the value
If value is blank needs to pick up the last date entered for that year
It’s an annual data so if I’m in 2022 the carry forward shouldn’t continue into 2023.
That’s why I’m having some difficulty with the formula required for carry forward.
For the YTD budget I’m looking for a formula that would pick up the last entered date for that year. For this example it would be December but would need to change as the years change. If I filter on any month in 2022 it would pick up December 2022 and if I filter on any month in 2023 it would pick up December 2023.