Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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).
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
75 | |
54 | |
37 | |
31 |
User | Count |
---|---|
99 | |
56 | |
50 | |
42 | |
40 |