Calculate difference between dates based on Month slicer

[WrxFlo]

Hi,

I have columns that show rental start and end dates for each serial number

I need to add a month slicer that shows days on rent for month selected.

So for example if I select May, PXT2180 will show 0. PXT1358 will show 16. PXT1328 will show 7.

if I select June, PXT2180 will show 30. PXT1358 will show 30. PXT1328 will show 0.

thanks

Tim 

 

[Anonymous · Accepted solution]

Hi @WrxFlo ,

I suggest you to try this code to create a measure to calculate date differences.

Measure = 
VAR _LIST = VALUES(DimDate[Date])
VAR _MINDATE = MIN(DimDate[Date])
VAR _MAXDATE = MAX(DimDate[Date])
VAR _ADDDIFF = ADDCOLUMNS('Table',"DateDiff",IF('Table'[Start Date] IN _LIST, DATEDIFF('Table'[Start Date],IF('Table'[End Date] in _LIST,'Table'[End Date], _MAXDATE),DAY),IF('Table'[Start Date]<=_MAXDATE&& 'Table'[End Date] = BLANK(),DATEDIFF(_MINDATE,_MAXDATE,DAY),0)))
VAR _SUMMARIZE = SUMMARIZE('Table',[Serial #],"DATEDIFF",IF( SUMX(FILTER(_ADDDIFF,[Serial #] = EARLIER('Table'[Serial #])),[DateDiff])=0,0,SUMX(FILTER(_ADDDIFF,[Serial #] = EARLIER('Table'[Serial #])),[DateDiff])+1))
RETURN
SUMX(_SUMMARIZE,[DATEDIFF])

Result is as below.

Best Regards,
Rico Zhou

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

[WrxFlo]

Thanks Rico Zhou. 

[NickolajJessen]

you might need to add a day deping on how you want to count the number of days