Graph a daily amount in between two dates

Question

The source table has a table with a single amount and a revenue start and revenue end date. I need to graph the amount over the period by day in PowerBI.

For example:

Looking at the second row the total amount is 730 but I need to calculate a daily rate and display this each day for the revenue period. So if I had a bar chart for this row I would need to show it as 16 April has 34.76, 17 April has 34.76 and so on until 6 May which is the revenue end date. I've tried using between dates but cant seem to get it working.

Answer 1

You can use Power BI's CALENDAR() function to create a table of dates ranging from the minimum revenue start date to the maximum revenue end date.

Dates = CALENDAR(MIN(BookFees[Revenue Start Date]),MAX(BookFees[Revenue End Date]))

Then you can create a calculated column in the Dates table for the daily revenue.

Daily Revenue = Calculate(SUM(BookFees[RevenueDayAmount]),FILTER(BookFees,BookFees[Revenue Start Date]<=Dates[Date] && BookFees[Revenue End Date]>= Dates[Date]))

Here is the resulting bar chart: