daily rental earnings by date

[waynewoodhead]

Hi,

I have a rental data set and want to show earnings per day across all contracts.  The data looks like

ContractNo	StartDate	EndDate	Daily Rate
123	1/1/2020	5/1/2020	5
456	4/1/2020	7/1/2020	10

What I want as an output is the summed daily rates for contracts active on each day 

1/1/2020	2/1/2020	3/1/2020	4/1/2020	5/1/2020	6/1/2020	7/1/2020	8/1/2020
5	5	5	15	15	10	10	0

Any help appreciated,  thanks

[V-pazhen-msft · Accepted solution]

@waynewoodhead 

Create a calculated column seems to be more straightforward for you.

Column = CALCULATE(SUM('Table'[Daily Rate]),FILTER('Table',EARLIER('Calendar'[Date])>='Table'[StartDate]&&EARLIER('Calendar'[Date])<='Table'[EndDate]))

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/Ef3pSJ337XdPpGkxVAg2...

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Refer to my blog:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

link for pbix : https://www.dropbox.com/s/excoqikgpyz5zvk/Current_employee.pbix?dl=0

[Greg_Deckler]

You would need a date table to use in columns of the matrix. Unrelated.

You could then create a measure like this:
Measure =
VAR __Date = MAX('Calendar'[Date])
RETURN
SUMX(FILTER('Table',__Date >= [StartDate] && __Date <=[EndDate]),[Daily Rate])

[waynewoodhead]

Hi Greg

I created the date table and added the measure to my data table.
When I put Date and Daily Rate into a view table the result is not correct.

Do I need to link the two tables first ?
Could you provide a sample PBIX ?
Much thanks
Wayne

[waynewoodhead]

Hi,

thanks,  I'm quite new to PBI so the help is great.

In which table do I create the Measure, Calendar or my data table, or elsewhere

Wayne