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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.