SUMX with visual filters

[Syndicate_Admin]

Good morning community, I write because I have a problem that I have been trying to solve for some time.

I have a table with some records of failures, which have start date and end date, I made a measurement to calculate the duration of the failure. The important thing about this measure is that if I select a specific month and the failure has as its starting date a day corresponding to the month prior to the selected one, I should take as the starting date for the calculation of the duration of the failure the first day of the month, this in order to be consistent with the filters that I am applying.

TiempoIncidenteMin =

WHERE iniTime = .MAX( MIN('Tickets Maximo Base'[creationdate]), MIN('Maximum Tickets Calendar'[Date]) + 1)

WHERE finTime = MIN( MIN('Tickets Maximo Base'[actualfinish]), .MAX('Maximum Tickets Calendar'[Date]))

WHERE diff = DATEDIFF(iniTime, finTime, MINUTE)

RETURN

diff

The problem is that when I add this measurement to a table visual, the fault durations are calculated correctly. However, in the totals it gives me a number that is not consistent, as if instead of adding what is in the column it is evaluated with the total of the other columns I am using. I would like to add all the durations of the failures in the total. I already tried using SUMX but this does not evaluate the month filters applied in the view.

Attached pbix in case anyone can help me.

https://drive.google.com/file/d/1f2qohUEb0l5mlHCxGEKQxNAv-ZqNwAxa/view?usp=sharing

[v-tangjie-msft · Accepted solution]

Hi @Syndicate_Admin ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Total_TiempoIncidenteMin = 
SUMX(VALUES('Tickets Maximo Base'[cinum]),[TiempoIncidenteMin])

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[lbendlin]

The important thing about this measure is that if I select a specific month and the failure has as its starting date a day corresponding to the month prior to the selected one, I should take as the starting date for the calculation of the duration of the failure the first day of the month, this in order to be consistent with the filters that I am applying.

I didn't get this part, can you please elaborate?