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 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 

 

 

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.

     

     

3 Replies

  • 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

     

  • 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.