Forum Discussion

WrxFlo's avatar
WrxFlo
Frequent Visitor
4 years ago
Solved

Calculate difference between dates based on Month slicer

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 w...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

NickolajJessen's avatar
NickolajJessen
Solution Sage
4 years ago

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