Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
danny_dmv
Frequent Visitor

How to DAX a measure to calculate the total number of days with overlapping date and gaps

I'm trying to calculate the total forecast proportional to the number of days active on contract. Each product has an annual target number though the contract can be part year, so its actual target will need to be adjusted proportionally. It's also common that a product can be on contract multiple times in a year, with date gaps in between. Occasionally, a new contract can be added with dates overlapping an existing contract, typically to extend the existing contract with new terms. 

My thought process is to create a filtertable1 to capture product contracts meeting the date slicer criteria. Then create a filtertable2 to calculate avg, sum... but in filtertable2, how do I count the total number of days without counting the dates in gap? I'm totally stuck, and I'd really appreciate if someone can share some pointers. Thanks!

 

Screenshot 2023-02-21 210955.png

0 REPLIES 0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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