display calculated values for a timespan

Hi sangrick,

You idea is to create a temporary table a small delta of 5 minutes, it's hard to compare the time wiout date. For example, 28.06.2017 00:27 is bigger than 27.06.2017 23:31 obviously. But 00:27 is less than  23:31 when we use time to compare.

So I post my solution, please follow the steps below.

1. Create a time table including all start_time and end_time, create a new table by clicking "New Table" under Modeling on Home Page.

TimeTable = UNION(DISTINCT('Fact'[Start_Date]),DISTINCT('Fact'[End_Date]))

2. Order the Start_Date column Ascending(right click the head name->Ascending), add a rank column, then get the period end time using the rank.

rank = RANKX(TimeTable,TimeTable[Start_Date],,ASC)

End_Time = LOOKUPVALUE(TimeTable[Start_Date],TimeTable[rank],TimeTable[rank]+1)

You will get the TimeTable like the screenshot.



3. Create A,B, C calculated columns using the formulas.

A = CALCULATE(COUNTROWS('Fact'),FILTER('Fact','Fact'[Customer]="A"&&'Fact'[Start_Date]<=TimeTable[End_Time]&&'Fact'[End_Date]>=TimeTable[End_Time]))

B = CALCULATE(COUNTROWS('Fact'),FILTER('Fact','Fact'[Customer]="B"&&'Fact'[Start_Date]<=TimeTable[End_Time]&&'Fact'[End_Date]>=TimeTable[End_Time]))

C = CALCULATE(COUNTROWS('Fact'),FILTER('Fact','Fact'[Customer]="C"&&'Fact'[Start_Date]<=TimeTable[End_Time]&&'Fact'[End_Date]>=TimeTable[End_Time]))

4. Create a stacked area chart using the TimeTable, select the start_time as Axis, and display it as category. Please see the following screenshot.

Best Regards,
Angelia