Forum Discussion

musoQ's avatar
musoQ
Regular Visitor
8 years ago
Solved

Creating a measure from values in two Date Slicers

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

5 Replies

    • musoQ's avatar
      musoQ
      Regular Visitor
      7 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

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

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      musoQ
      Regular Visitor
      8 years ago

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