Help Solving a calculated measure for burndown measure exceeding date range

[TubaKinch]

Hello all - Looking for some help that I've been staring at this too long, and it's probably easy, and I'm a novice DAX user.

I have the following code which is creating a "ideal budget burndown", which is straightline from the beginning of the year (total budget), and reducing to 0 at the end of the year.

_Ideal_Budget_Burndown =

VAR __MinCalendarDate = MIN('TEST_Calendar'[Date])

VAR __MaxCalendarDate = MAX('TEST_Calendar'[Date])  

VAR __EndingFiscalCalendarDate =

    CALCULATE (

        LASTDATE('TEST_Calendar'[Date]),

        ALLEXCEPT(TEST_Calendar,TEST_Calendar[Date]))-1

VAR __Ideal_Budget_Burndown =

    CALCULATE(

        SUM('TEST_Project Details'[Budget]),

        FILTER (

            ALLSELECTED('TEST_Calendar'[Date]),'TEST_Calendar'[Date] >= __MinCalendarDate ))

RETURN

__Ideal_Budget_Burndown

In this example, the budget I'm working with is ~8125 a month, totalling $97500 for the year. 
The Calendar table is a DAX generated table of calendar dates from 1/1/2023 to 1/1/2024.  I intentionally included 1/1/2024 so we can get to "zero".

This code creates the following chart:



The issue with the chart is JAN 2024 is missing, and the remainder of 2024 represents the total budget that is the burndown example, which should not exist.   

This is likely a stupid filter I've misaligned, or a time-intelligence feature that I need to work around somehow.

Any help would be appreciated.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[TubaKinch]

I put together a sample power bi workbook which should help.  Here's a onedrive link to the file. https://1drv.ms/u/s!AuWIDCM8OerBhs03N9xk_o7yY-GLpQ?e=84p73B   

Documented on the page is an example, bad and desired.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file.

Hope this helps.

[TubaKinch]

Thanks Ashish - this gives me exactly what I needed, and I'll  walk through the DAX to be sure I understand it.  @rbriga thank you too, your's was very helpful in this conversation also.

[Ashish_Mathur]

You are welcome.

[TubaKinch]

@rbriga thanks for the suggestions. 

For #1 - I am connected to the calendar, which is part of what confuses me, as the calendar has all of 2023 and Jan 1 2024.   I'm ok with seeing 2024, but only Jan 2024 (which was missing), and the rest of 2024 shoudlnt be there at all.    The goal being I need to see the burndown visibliy show "0" on Jan 2024. (it's really dec 31, but all the data is aggregated already at monthly before I receive it)

For #2 - I would use suggestion #2 but unable because when I expand this beyond the test data set, some budgets will start mid-year, not always divisible by 12. (but I like this idea)

Do you have another suggestion to augment on #1, so I "can" see Jan 2023->Jan 2024 (inclusive) to show the complete burdown to 0?



Thanks!!!

[rbriga]

Not seeing January at 0 is an expected result.

My suggestions:

A) If you had a budget fact table- year, budget. Connect it to the calendar table using the year.

Then, 2024 won't show up unless it has a budget value.

or

B) Define a single, yearly budget as a measure.

The burndown would be:

(13-MONTH(MAX('TEST_Calendar'[Date])) * DIVIDE([Yearly Budget],12)

This would return 1/12 of the budget per month from the end of the year.