Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Sarhar_1921
Regular Visitor

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

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. 
      Sarhar_1921_1-1702791408247.png

       

  • 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 

IDStateCreated DateChanged DateQuantity
1160NY09/05/2023 11:30:00 AM 10
1161NY09/05/2023 4:00:00  AM09/08/2023  10:00:00  PM20
1162NY09/06/2023 8:00:00  AM09/19/2023 4:00:00  PM20
1163NY09/06/2023 7:00:00  PM09/10/2023 6:00:00  AM60
1164NY09/08/2023 10:00:00  PM09/20/2023 11:00:00  AM60
1165NY09/10/2023 6:00:00  AM09/21/2023 4:00:00  PM80
1166NY09/17/2023 9:00:00  PM 50
1167NY09/17/2023 11:30:00  PM 30
1168NY09/22/2023 2:00:00  PM 35
1169CA09/05/2023 11:30:00 AM 10
1170CA09/05/2023 4:00:00  AM09/08/2023  10:00:00  PM100
1171CA09/06/2023 8:00:00  AM09/19/2023 4:00:00  PM150
1172CA09/06/2023 7:00:00  PM09/10/2023 6:00:00  AM180
1173CA09/08/2023 10:00:00  PM09/20/2023 11:00:00  AM200
1174CA09/10/2023 6:00:00  AM09/21/2023 4:00:00  PM300
1175CA09/17/2023 9:00:00  PM 50
1176CA09/17/2023 11:30:00  PM 30
1177CA09/18/2023 2:00:00  PM 35
1178CA09/05/2023 11:30:00 AM 10
1179TX09/05/2023 4:00:00  AM09/08/2023  10:00:00  PM135
1180TX09/06/2023 8:00:00  AM09/19/2023 4:00:00  PM142
1181TX09/06/2023 7:00:00  PM09/10/2023 6:00:00  AM155
1182TX09/08/2023 10:00:00  PM09/20/2023 11:00:00  AM165
1183TX09/10/2023 6:00:00  AM09/21/2023 4:00:00  PM175
1184TX09/17/2023 9:00:00  PM 50
1185TX09/17/2023 11:30:00  PM 30
1186TX09/18/2023 2:00:00  PM 35
1187TX09/23/2023 11:30:00  PM 40
1188TX09/23/2023 2:00:00  PM 40

 

Expected Output

StateQuantity
NY160
CA650
TX482

 

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

Sarhar_1921_2-1702791597010.png

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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

 

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. 

Sarhar_1921_2-1703012197819.png

 

 

Data for the report

IDStateCreated DateChanged DateQuantityStart HourEnd Hour
1160NY9/5/2023 11:30 101111
1161NY9/5/2023 4:009/8/2023 22:002044
1162NY9/6/2023 8:009/19/2023 16:002088
1163NY9/6/2023 19:009/10/2023 6:00601919
1164NY9/8/2023 22:009/8/2023 22:35602222
1165NY9/10/2023 6:009/21/2023 16:008066
1166NY9/17/2023 21:00 502121
1167NY9/17/2023 23:30 302323
1168NY9/22/2023 14:00 351414
1169CA9/5/2023 11:30 101111
1170CA9/5/2023 4:009/8/2023 22:0010044
1171CA9/6/2023 8:009/19/2023 16:0015088
1172CA9/6/2023 19:009/10/2023 6:001801919
1173CA9/9/2023 22:009/20/2023 11:002002222
1174CA9/10/2023 6:009/21/2023 16:0030066
1175CA9/17/2023 21:00 502121
1176CA9/17/2023 23:30 302323
1177CA9/18/2023 14:00 351414
1178CA9/5/2023 11:30 101111
1179TX9/5/2023 4:009/8/2023 22:0013544
1180TX9/6/2023 8:009/19/2023 16:0014288
1181TX9/6/2023 19:009/10/2023 6:001551919
1182TX9/8/2023 22:009/20/2023 11:001652222
1183TX9/10/2023 6:009/21/2023 16:0017566
1184TX9/17/2023 21:00 502121
1185TX9/17/2023 23:30 302323
1186TX9/18/2023 14:00 351414
1187TX9/23/2023 23:30 402323
1188TX9/23/2023 14:00 401414

 

Thank you. I would appreciate any solution

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors