Total number of days between dynamic dates

[helpa]

I am looking to calculate the total number of 'possible' days for a group of people based on StartDate, and EndDate which changes based on the period I'm looking at compared to their actual PositionStartDate (PSD), and PositionEndDate (PED). I also only want to use the first sequencehistory value

For Example,

for January 2022 the total possible days would be 31.

-if a person had a PSD of 31/12/21, and PED of 28/02/22 then the number should be 31 as their PSD is before the start of the period, and PED is after the end of the period
-if a person had a PSD of 10/12/21, and PED of 31/01/22 then the number should be 21

The measure I have created so far works when inside a table, however the total number is incorrect (it is using the maximum and minimum dates in my dates table over all the rows in my Position table.

Which returns

The values are correct however 184,890 / 30 = 6163 which is the number of positions in the table with a historysequence of 1

I can get the correct answer when I apply filters to the table, namely TotalPossibleDays > 0  and version

But I'm unsure how to do that inside the measure at the moment. 

Thanks in advance

[v-chenwuz-msft]

Hi @helpa ,

A DAX HASONEVALUE(<columnName>) maybe helpful to you,this is allowed you to control the total row. You can assign a separate calculation to total row.

I suspect you may need to use the aggregated table created by summarize(). Some code like the following:

code =
VAR _summarize =
    CALCULATETABLE(
        SUMMARIZE(
            'Reporting DimPosition',
            [PositionID],
            [PositionStartDate],
            [PositionEndDate],
            "total", [#TotalPossibleDays]
        ),
        KEEPFILTERS( [#TotalPossibleDays] )
    )
VAR _sumx =
    SUMX( _summarize, [total] )
RETURN
    IF(
        HASONEVALUE( 'Reporting DimPosition'[PositionID] ),
        [#TotalPossibleDays],
        _sumx
    )

You need to modify this code to suit your model.

Best Regards

Community Support Team _ chenwu zhu

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

[amitchandak]

@helpa ,

You have to date diff between

Sumx(table, datediff(max(minStartDate, Mindate), Min(Maxenddate, maxdate) ,day) )

Use you first 4 var and return a sumx like this with filter

[helpa]

Thank you for the reply @amitchandak 

I created the below #TotalPossibleDaysNEW 

Which returned for days amounts which should be 0. 

This is what I tried to adjust for with my StartDate and EndDate Variables in my inital measure. 

As if their PED is before the Period we should ignore, and if their PSD is after the Period we should ignore. 

[helpa]

I adjusted the above to try and remove those negatives with 

However the total is still showing [Number of Days in Month] * 6163 

[helpa · Accepted solution]

I managed to get the expected answer by not using MIN() and MAX() around PSD and PED