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
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
)
Bump, any other solutions to this one?
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:
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |