Using Date Slicer in Measure outside report context dates

[NMP]

Hello - apologies in advance if this has been answered and for the length of the post; I have searched on the issue and found related topics but nothing that solves my problem and after four days I am a bit frustrated. I am extremely new to Power BI so it is unclear if my problem is complex or simple.  There are a lot of words that get to the heart of my problem that I will state first to save time. “The issue is that I cannot pull in the contracts where the Current contract date is prior to the report context START_DATE.  However, where I have an active contract, I need to accumulate the active number of days between the report START_DATE and the report END_DATE to calculate the total GAD (GAD_PER_DAY * DELTA DAYS) and the resulting revenue and cost.”

I have a sample of my Power BI Desktop file, I found a way to attach it to this post

(link here:  https://1drv.ms/u/s!ArOcIzln0vPO7Vrvz9Joa4P8GZJD). 

It contains shipment and contract quote data - this is the file that users will interact with, not a report or published dashboard.  It may or may not be important that I originally performed all the calculations in SQL in a test database before I was informed the Shipment and Quote production versions have a firewall the prevents any interconnection outside PBI.

The specific issue I first need to address is with the quote data; it has a key that identifies the characteristics of a quote (called BAS_ID) and can be used to compare it other quotes with the same customer, service, origin, destination.  On each row of the quote table (PQR_SUMMARY) I have a "Current Contract Date" and "Next Contract Date".  The Current date is mapped to the DATE field in my Calendar table and the Next date is an inactive link.  Current date represents the date the quote row was submitted into the pricing system; the Next contract date represents a renegotiation of the contract that will replace the current contract.  This is important for two reasons a) shipments need to be matched to the correct quote row and b) expectations of the quote need to be determined for the duration the quote was “live”.  This question is in regard to b).

In the screen shot at the bottom of the window, I have one example of a contract that was re-negotiated multiple times between 2014 and 2018.

You can see the Current Contract Date, Next contract Date, and the next column is GAD_PER_DAY. 

I have defined a measure called START_DATE as FIRSTDATE(Dates_New[Date])

And END_DATE as LASTDATE(Dates_New[Date]).  These correctly respond to the min and max dates in the date slicer.

What I need to do is accumulate the GAD_PER_DAY over the difference in active report dates.  I finally (after a lot of trial and error) came up with the following for calculating the date difference;

DELTA_DATE =  DATEDIFF(MAX(MAX(PQR_SUMMARY[CURRENT_CONTRACT_DATE]),[START_DATE]),MIN(MAX(PQR_SUMMARY[NEXT_CONTRACT_DATE]),[END_DATE]),DAY)

For instance, if the visual report start date is 1/28/2013 and the contract start date is after 1/28/2013, then I take the difference between the contract start date and either the Next contract date or the report end date … whichever comes first.

The issue is that I cannot pull in the quote contracts where the Current date is prior to the START_DATE.  However, I need to accumulate the active number of days between the report START_DATE and the minimum of the Next contract date or report END_DATE and actively calculate the total GAD (GAD_PER_DAY * Days) and the resulting revenue and cost. 

It seems like I should be able to use one of the remove filter functions in CALCULATE (like ALL[Dates new]) or something but I have not been able to determine a syntax that keeps the context of START_DATE and END_DATE but also pulls in the contract data where the CURRENT date is < START_DATE and NEXT_DATE > START_DATE and < END_DATE. 

Any and all help is GREATLY appreciated!  Please let me know if additional details are required to address the question.

Regards,

[v-yulgu-msft]

Hi @NMP,

Unfortunately, the posted link failed to download your sample .pbix file.

Regards,

Yuliana Gu

[NMP · Accepted solution]

I had several people test the link and it worked for about half .  Fortunately one of the testers is better at googling questions than I am and found a solution in StackOverflow under the name "calculate-event-durations-to-only-part-within-sliced-period"

I just finished verifying the method provides the correct solution and was going to update this post - so excellent timing!

The key was to create a disconnected DATE table that is used in the slicer for the report.  I can then calculated the difference between max(current contract date, slicer start date) and min(contract end date, slicer end date) without removing the data rows that have active contract after the slicer start date.  I can provide more detail if anyone else has the issue or is unable to find the stack overflow solution.

thank you for looking!

nmp

[v-yulgu-msft]

Hi @NMP,

Glad to hear that you have resolved the problem. Thanks for your sharing.

Regards,

Yuliana Gu