Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
TubaKinch
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:

Screenshot 2023-06-17 174208.jpg

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

Hi,

You may download my PBI file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

Hi,

You may download my PBI file.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TubaKinch
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?

Screenshot 2023-06-18 161532.jpg

Thanks!!!

rbriga
Impactful Individual
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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.