Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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]))
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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]))
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
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
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |