Value Carry Forward When value is Zero

DAX DAX Calculations
1

Hi

Two issues I am currently facing with some financial data

  1. 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.

  2. 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.

Thanks
Financials_Test.pbix (200.9 KB)
Reporting Example.xlsx (16.1 KB)

2

Hi @olson.michele ,

Thank you for reaching out to the community.

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:

image
image1547×860 109 KB

image
image1550×741 88.2 KB

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.

Cheers,

Enterprise DNA Support Team

3

Hi @olson.michele - For your first requirement. Have tried something like below.

  1. Created a new measure similar to YTD Act.

YTD Total = sum('Trial Balance'[Local Currency])

  1. Modified the existing YTD Act formula as below.
YTD Act =

var MaxDate = CALCULATE(LASTNONBLANK(Dates[MonthnYear],[YTD Total]),all(Dates))

var FinalVal = CALCULATE([YTD Total],all(Dates),Dates[MonthnYear] = MaxDate)

return

FinalVal

This is giving me correct values. Can try at your end. Find the solution file attached.

Financials_Test.pbix (200.9 KB)

Regarding your second requirement, please provide more information on the requirement.

Thanks
Ankit J

4
  1. 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.
  2. 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.

5

Hi

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.

Thanks

Financials_Test V1.pbix (202.9 KB)