Creating a measure from values in two Date Slicers

[musoQ]

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

[Ashish_Mathur]

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

[musoQ]

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.

[Ashish_Mathur · Accepted solution]

You are welcome.  If my reply helped, please mark it as Answer.

[PattemManohar]

@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...

[musoQ]

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).