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.

     

     

Anonymous's avatar
Anonymous
Not applicable
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.

 

 

  • WrxFlo's avatar
    WrxFlo
    Frequent Visitor
    4 years ago

    Thanks Rico Zhou.