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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
zeronet
Frequent Visitor

Need help with a measure which calculates a burn rate for tracking project budgets

Hi,

 

I have a simple measure which takes timesheet data, and uses that information to calculate the burn rate per week on multiple projects. I do this by summing all the values in the 'GI Time'[Cost] from the selected date and the preceding month, dividing by the amount of working days in that month, and then multiplying by 5 (5 day work week).

 

It works, the issue is, the graph cuts off at the last date in the timesheets, take a look at the red circle. Basically, the cutoff point is the last date in 'GI Time'[Transaction Date] for that [Invoice Category Merged] value.

 

Is there a way to modify this measure so that it continues past the last transaction date in 'GI Time' and instead goes to the current date? You'd expect that since there's no data, the average calculated will trail down towards zero, and end up at zero after a month.

 

'Calendar'[Date] is a column which contains every single date

'Calendar'[Up to Today] is a column which contains True and False, false if the date is less than today, I use this to cap the chart

'Key Measures'[IsWorkingDayTrue] is a measure which checks if the date is a working day

'GI Weekly Tracker'[Invoice Category Merged] is a column containing the various project tasks, in the graph below, each of these tasks is represented by a different line.

 

Any help for this novice? Much appreciated.

 

Average Burn Rate (4 Weeks) = 
VAR SelectedInvoiceCategory = SELECTEDVALUE('GI Time'[Invoice Category])
VAR StartDate = CALCULATE(MAX('Calendar'[Date]), 'Calendar'[Up to Today] = TRUE)
RETURN
    CALCULATE(
        5*SUM('GI Time'[Cost]) / 'Key Measures'[IsWorkingDayTrue],
        DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -1, MONTH),
        'GI Weekly Tracker'[Invoice Category Merged] = SelectedInvoiceCategory
    )

 

zeronet_0-1684905408466.png

 

3 REPLIES 3
zeronet
Frequent Visitor

Bump, any other solutions to this one?

Mrxiang
Helper II
Helper II

To modify the measure to continue past the last transaction date, you can add a new column to the data table that represents the current date, and then use this new column in the calculation for the burn rate.
Here's an example of how you can modify the measure:

Add a new column to the data table that represents the current date. You can do this by creating a new column in the data table, and setting the default value of this column to the current date.
Modify the measure to use the new column for the current date. You can do this by adding a new line of code that calculates the average using the new column for the current date, instead of using the 'GI Time'[Transaction Date] column.
Here's an example of how the modified measure would look like:

AverageBurnRatePerWeek = 
    SUMX('GI Time', 
           FILTER('Timesheets', 
                    DATE(TimePeriod, '2022-01-01') = DATE(TODAY(), '2022-01-01') 
                    && 'GI Time'[Cost] > 0), 
           Average('GI Time'[Cost]))

AverageBurnRatePerWeek = 
    SUMX('GI Time', 
           FILTER('Timesheets', 
                    DATE(TimePeriod, '2022-01-01') = DATE(TODAY(), '2022-01-01') 
                    && 'GI Time'[Cost] > 0), 
           Average(Dates(TimePeriod, '2022-01-01'::Date, '2022-01-31'::Date))

With this modification, the measure will continue past the last transaction date and will use the current date instead. The average calculated will trail down towards zero after a month as you expected.

Hmm, I'm still not sure what to do after creating the additional column 'GI Time'[TodayDate] which is a simple:

TodayDate = TODAY() column.
 
The code from your response gives me syntax errors in relation to the ":" and I don't have a Timesheets filter either, the Timesheets are 'GI Time'.
 
Thanks for the help though, might try again after dinner. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors