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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.