Need some help with a DAX measure that can calculate total shifts between two dates.

[bwang2]

Below are two example of filtered data set, Total shift is what I am trying to calculate. 

All my data are in the descending order by DateShiftKey, so that on the same date, DS is always on top of NS.

My approch is to isolate the "first" and "last" shift, what falls in between is irrelevant. I am having trouble to isoloate the "first" and "last" shift information in each sample table. I have also pasted the DAX I wrote but it's not working at where I crossed out, I need soemthing to indentify the first and last shift.

Total Shifts Count =

VAR StartDate = MIN('Table'[ReportingDate])

VAR EndDate = MAX('Table'[ReportingDate])

VAR TotalDays = DATEDIFF(StartDate,EndDate,DAY)+1

VAR ShiftDatrt = IF(FIRSTNONBLANK(Table[Shift],0)= "DS",0,1)

VAR ShiftEnd = IF(LASTNONBLANK(Table[Shift],0)= "DS",1,0)

RETURN

    (TotalDays * 2) - ShiftDatrt - ShiftEnd

Filter one
Task	DateShiftKey	ReportingDate	Shift		total shift=6
1	20240103NS	1/3/2024	NS
2	20240104DS	1/4/2024	DS
3	20240104NS	1/4/2024	NS
4	20240105DS	1/5/2024	DS
5	20240106DS	1/6/2024	DS
6	20240106DS	1/6/2024	DS
7	20240106DS	1/6/2024	DS
Filter two
Task	DateShiftKey	ReportingDate	Shift		total shift=13
1	20240109DS	1/9/2024	DS
2	20240110DS	1/10/2024	DS
3	20240110DS	1/10/2024	DS
4	20240110DS	1/10/2024	DS
5	20240112NS	1/12/2024	NS
6	20240113DS	1/13/2024	DS
7	20240113NS	1/13/2024	NS
8	20240113NS	1/13/2024	NS
9	20240113NS	1/13/2024	NS
10	20240114DS	1/14/2024	DS
11	20240114DS	1/14/2024	DS
12	20240114NS	1/14/2024	NS
13	20240115DS	1/15/2024	DS

[v-yohua-msft · Accepted solution]

Hi, @bwang2 
Maybe you can try the following DAX expression, I've made some improvements to your Measure by judging the first and last shifts before calculating them.

Total Shifts Count = 
VAR StartDate = MIN('Table'[ReportingDate])
VAR EndDate = MAX('Table'[ReportingDate])
VAR TotalDays = DATEDIFF(StartDate,EndDate,DAY)+1
VAR ShiftStart = CALCULATE(MIN('Table'[Shift]),FILTER(ALL('Table'),'Table'[ReportingDate]=MIN('Table'[ReportingDate])))
VAR ShiftEnd = CALCULATE(MAX('Table'[Shift]), FILTER(ALL('Table'),'Table'[ReportingDate]=MAX('Table'[ReportingDate])))
VAR ShiftStartValue = IF(ShiftStart = "DS",0,1)
VAR ShiftEndValue = IF(ShiftEnd = "DS",1,0)
RETURN
    (TotalDays * 2) - ShiftStartValue - ShiftEndValue

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bwang2]

@v-yohua-msft Thanks!!! it worked