DAX Row Total is Wrong For Sum Date Difference

Question

I have table which includes start_date and end _date. I want to get date difference between these columns by minute. There is also date and time slicer and if max date slicer is smaller than end_date, I have to get date difference between start date and max date slicer + max time slicer.

I created a measure about this but row total is not true. How can I fix it?

this is my measure I wrote;

Zaman 3 = 
var MaxDateTime =MAX(vwDimDate[Date]) + TIME(HOUR(MAX(vwDimTime[Time])), MINUTE(MAX(vwDimTime[Time])), SECOND(MAX(vwDimTime[Time])))
var sonuc1 = SUMX(vwFactATM_STATUS_LOG,DATEDIFF(vwFactATM_STATUS_LOG[STARTING_DATE],vwFactATM_STATUS_LOG[END_DATE],MINUTE))
var sonuc2 = SUMX(vwFactATM_STATUS_LOG,DATEDIFF(vwFactATM_STATUS_LOG[STARTING_DATE],MaxDateTime,MINUTE))
var deger = IF(MaxDateTime>MAX(vwFactATM_STATUS_LOG[END_DATE]),sonuc1,sonuc2)
//var a=MAX(vwFactATM_STATUS_LOG[END_DATE])
return deger

And table output;

As You see row total is not true. How can I fix it?

Hüseyin Hüseyin · Accepted Answer · 2021-05-04T17:32:03

I solved the problem. We need calculate inside "if block" look I how I corrected

Var MaxDate=CALCULATE(MAX(vwDimDate[Date]) + TIME(HOUR(MAX(vwDimTime[Time])), MINUTE(MAX(vwDimTime[Time])), SECOND(MAX(vwDimTime[Time]))),ALLSELECTED(vwDimDate),ALLSELECTED(vwDimTime))

VAR Tarih=Sumx( vwFactATM_STATUS_LOG, If(MaxDate<vwFactATM_STATUS_LOG[END_DATE], DATEDIFF(vwFactATM_STATUS_LOG[STARTING_DATE],MaxDate, MINUTE) , DATEDIFF(vwFactATM_STATUS_LOG[STARTING_DATE],vwFactATM_STATUS_LOG[END_DATE],MINUTE) ))

return Tarih

DAX Row Total is Wrong For Sum Date Difference

1 Answers