topic Re: Power BI Dax to calculate sum of quantity based on the two date time fields. in DAX Commands and Tips

Power BI Dax to calculate sum of quantity based on the two date time fields.

Sarhar_1921 — Mon, 18 Dec 2023 00:09:19 GMT

Hello All,

I would like to seek help in calculating the sum of quantity based on the below condition.

I’ve “created date” as data time field used in advanced filter pane with option to select the date and time format as shown below.
- Date Time selected in the example is (09/05/2023 11:00 PM to 09/18/2023 11:00 PM). User has the ability to change the date time selection using the advanced filtering.
Need to calculate the sum of quantity only when the “Changed date” (Date Time format) is greater than the max of “Created Date” (Date Time Format) field selected using the advanced filter option.
- Sum of quantity will be calculated only when the “Changed Date” is greater than max of “Created Date” (09/18/2023 11:00 PM).
“Created Date” will be dynamically changed based on the selected using Advanced Filtering. Need to get the max of created date time
I’ve provided data and expected output for the condition mentioned above. Also, Attached the screenshot of the data. Highlighted in yellow are the data matches the condition.

I would appreciate any help on the same. Thank you!

Data

ID	State	Created Date	Changed Date	Quantity
1160	NY	09/05/2023 11:30:00 AM		10
1161	NY	09/05/2023 4:00:00 AM	09/08/2023 10:00:00 PM	20
1162	NY	09/06/2023 8:00:00 AM	09/19/2023 4:00:00 PM	20
1163	NY	09/06/2023 7:00:00 PM	09/10/2023 6:00:00 AM	60
1164	NY	09/08/2023 10:00:00 PM	09/20/2023 11:00:00 AM	60
1165	NY	09/10/2023 6:00:00 AM	09/21/2023 4:00:00 PM	80
1166	NY	09/17/2023 9:00:00 PM		50
1167	NY	09/17/2023 11:30:00 PM		30
1168	NY	09/22/2023 2:00:00 PM		35
1169	CA	09/05/2023 11:30:00 AM		10
1170	CA	09/05/2023 4:00:00 AM	09/08/2023 10:00:00 PM	100
1171	CA	09/06/2023 8:00:00 AM	09/19/2023 4:00:00 PM	150
1172	CA	09/06/2023 7:00:00 PM	09/10/2023 6:00:00 AM	180
1173	CA	09/08/2023 10:00:00 PM	09/20/2023 11:00:00 AM	200
1174	CA	09/10/2023 6:00:00 AM	09/21/2023 4:00:00 PM	300
1175	CA	09/17/2023 9:00:00 PM		50
1176	CA	09/17/2023 11:30:00 PM		30
1177	CA	09/18/2023 2:00:00 PM		35
1178	CA	09/05/2023 11:30:00 AM		10
1179	TX	09/05/2023 4:00:00 AM	09/08/2023 10:00:00 PM	135
1180	TX	09/06/2023 8:00:00 AM	09/19/2023 4:00:00 PM	142
1181	TX	09/06/2023 7:00:00 PM	09/10/2023 6:00:00 AM	155
1182	TX	09/08/2023 10:00:00 PM	09/20/2023 11:00:00 AM	165
1183	TX	09/10/2023 6:00:00 AM	09/21/2023 4:00:00 PM	175
1184	TX	09/17/2023 9:00:00 PM		50
1185	TX	09/17/2023 11:30:00 PM		30
1186	TX	09/18/2023 2:00:00 PM		35
1187	TX	09/23/2023 11:30:00 PM		40
1188	TX	09/23/2023 2:00:00 PM		40

Expected Output

State	Quantity
NY	160
CA	650
TX	482

Snapshot of Data. Highlighted in yellow are the data matches the condition mentioned above.

Re: Power BI Dax to calculate sum of quantity based on the two date time fields.

lbendlin — Mon, 18 Dec 2023 22:06:07 GMT

You neglected to mention that you want to ignore rows with blank Changed Date.

Qty = var md = maxx(filter(Data, not ISBLANK([Changed Date])),[Created Date]) return sumx(filter(Data,[Changed Date]>md),[Quantity])

Re: Power BI Dax to calculate sum of quantity based on the two date time fields.

Sarhar_1921 — Tue, 19 Dec 2023 18:57:13 GMT

Hello,

Thanks for the response and i appreciate your time for providing the solution.

This measure works fine, but there is one issue in calcualting the total value. Individual record count looks good in the table visual. In Total, I'm expecting the total count of 425, but I got the total of 365 using this measure. Count of 60 missing for the record when the max of created date is greater than changed date.

Please find below the screenshot.

Data for the report

ID	State	Created Date	Changed Date	Quantity	Start Hour	End Hour
1160	NY	9/5/2023 11:30		10	11	11
1161	NY	9/5/2023 4:00	9/8/2023 22:00	20	4	4
1162	NY	9/6/2023 8:00	9/19/2023 16:00	20	8	8
1163	NY	9/6/2023 19:00	9/10/2023 6:00	60	19	19
1164	NY	9/8/2023 22:00	9/8/2023 22:35	60	22	22
1165	NY	9/10/2023 6:00	9/21/2023 16:00	80	6	6
1166	NY	9/17/2023 21:00		50	21	21
1167	NY	9/17/2023 23:30		30	23	23
1168	NY	9/22/2023 14:00		35	14	14
1169	CA	9/5/2023 11:30		10	11	11
1170	CA	9/5/2023 4:00	9/8/2023 22:00	100	4	4
1171	CA	9/6/2023 8:00	9/19/2023 16:00	150	8	8
1172	CA	9/6/2023 19:00	9/10/2023 6:00	180	19	19
1173	CA	9/9/2023 22:00	9/20/2023 11:00	200	22	22
1174	CA	9/10/2023 6:00	9/21/2023 16:00	300	6	6
1175	CA	9/17/2023 21:00		50	21	21
1176	CA	9/17/2023 23:30		30	23	23
1177	CA	9/18/2023 14:00		35	14	14
1178	CA	9/5/2023 11:30		10	11	11
1179	TX	9/5/2023 4:00	9/8/2023 22:00	135	4	4
1180	TX	9/6/2023 8:00	9/19/2023 16:00	142	8	8
1181	TX	9/6/2023 19:00	9/10/2023 6:00	155	19	19
1182	TX	9/8/2023 22:00	9/20/2023 11:00	165	22	22
1183	TX	9/10/2023 6:00	9/21/2023 16:00	175	6	6
1184	TX	9/17/2023 21:00		50	21	21
1185	TX	9/17/2023 23:30		30	23	23
1186	TX	9/18/2023 14:00		35	14	14
1187	TX	9/23/2023 23:30		40	23	23
1188	TX	9/23/2023 14:00		40	14	14

Thank you. I would appreciate any solution