cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Help Solving a calculated measure for burndown measure exceeding date range

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.

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
7 REPLIES 7
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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.

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

@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!!!

Impactful Individual

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.

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors