Forum Discussion

leodm's avatar
leodm
Frequent Visitor
3 years ago
Solved

Difference between dates on different rows and same column AFTER filtering

Database sample   Data de início real Squad Art 03/01/2022 17:30:00 Squad1 Art1 03/01/2022 22:00:43 Squad2 Art1 03/01/2022 22:30:00 Squad3 Art2 04/01/2022 09:00:00 Squad4 #...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi leodm 

    try to add plot all the columns with a measure like:

    Result = 
VAR _date = MAX(TableName[Date])
VAR _datepre =
MAXX(
    FILTER(
        ALLSELECTED(TableName),
        TableName[Date]<_date
    ),
     TableName[Date]
)
VAR _Difference= _date - _datepre
VAR _Days = INT(_Difference)
VAR _Hours = HOUR(_Difference)
VAR _Minutes = MINUTE(_Difference)
VAR _Seconds = SECOND(_Difference)
VAR _DaysToHours = _Days * 24
VAR _TotalHours = _DaysToHours + _Hours
RETURN
IF(
    _datepre<>BLANK(),
    FORMAT(_TotalHours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds,"00"),
    " "
)

     

    it worked like:

     

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi leodm 

try to add plot all the columns with a measure like:

Result = 
VAR _date = MAX(TableName[Date])
VAR _datepre =
MAXX(
    FILTER(
        ALLSELECTED(TableName),
        TableName[Date]<_date
    ),
     TableName[Date]
)
VAR _Difference= _date - _datepre
VAR _Days = INT(_Difference)
VAR _Hours = HOUR(_Difference)
VAR _Minutes = MINUTE(_Difference)
VAR _Seconds = SECOND(_Difference)
VAR _DaysToHours = _Days * 24
VAR _TotalHours = _DaysToHours + _Hours
RETURN
IF(
    _datepre<>BLANK(),
    FORMAT(_TotalHours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds,"00"),
    " "
)

 

it worked like:

 

leodm's avatar
leodm
Frequent Visitor
3 years ago

Hi FreemanZ thanks for you answer,

I was able to get correct results with your provided Measure, awesome!


The only thing I couldn't do is show it in a graph. Is it because graphs can't display time like that? How could I use these results to show days & hours in graphs? Thank you.

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi leodm 

    because the result of the measure is text, to plot graphically, try to return _Difference directly.

    • leodm's avatar
      leodm
      Frequent Visitor
      3 years ago

      Hi FreemanZ 

       

      I tried outputting the Result in hours just to generate a graph:

       


      The values are correct on the table, but for some reason the graph only shows the last value of the month.


      I need one graph to show the AVERAGE and another one to show the TOTAL within a certain period. Could you help me with this one?