Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All, been doing some searching and was hoping that somebody out there may be able to assist.
I have a customer that is looking to get a measure of funding based on the count of months calculated from values in a pair of Date Slicers.
The data in the Total Funding table looks like this:
Donor Total Funding Start Date Finish Date
Company X $50,000 01/03/2018 31/07/2018
Company Y $12,000 01/07/2018 30/06/2019
Company Z $24,000 01/01/2018 30/06/2018
If the customer selects 01/01/2018 in the Start Date slicer and 31/12/2018 in the Finish Date Slicer then the measure needs to equal $80,000 (Company X = $50,000 + Company Y = $6,000 + Company Z = $24,000)
As a second example, if the customer selects 01/07/2018 in the Start Date slicer and 30/06/2019 in the Finish Date Slicer then the measure needs to equal $22,000 (Company X = $10,000 + Company Y = $12,000 + Company Z = $0)
To add an additional layer of complexity, I have to replicate the same measure in a Target Funding Table. I have already set up the Start and Finish Date Slicers in the Target Funding table to sync with the Total Funding table. Its the DAX on the measures I'm struggling with.
Would appreciate any assistance on this one. Thanks in advance.
Q
Solved! Go to Solution.
You are welcome. If my reply helped, please mark it as Answer.
Hi,
You may refer to my solution in this PBI file.
Hope this helps.
Thanks for the calculated table concept @Ashish_Mathur. You have got me back on track in coming up with the final solution. Appreciate your assistance.
You are welcome. If my reply helped, please mark it as Answer.
@musoQ Just want to clarify.... about your first example...
Company X = $50,000 + Company Y = $6,000 + Company Z = $24,000
How come the Compan Y value is $6000 ? It should be $12000 right ? Please confirm...
Proud to be a PBI Community Champion
Thanks for the query @PattemManohar the first example where Company Y is $6,000 is down to the slicer selection of 01/01/2018 to 31/12/2018.
Company Y donates $12,000 in total to be used from 01/07/2018 to 30/06/2019 (or $1,000 per month over a 12 month period).
When the slicer selections are set to Start Date 01/01/2018 and Finish Date 31/12/2018 this is only 6 months of Company Y's donation period (01/07/2018 and 30/12/2018) resulting in only $6,000 ($1,000 per month for 6 month period).
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
107 | |
99 | |
99 | |
38 | |
37 |
User | Count |
---|---|
157 | |
121 | |
73 | |
73 | |
63 |