cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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

1 ACCEPTED SOLUTION
Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Super User

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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.

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

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

Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion

Regular Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors