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
SuperFave
New Member

Rolling Sum for 24 hour Date/Time window

I have some data for deliveries and weight that I would like to aggregate into a rolling sum for each delivery line.

I tried the below code, but it is only presenting the row 

This is what I have now for Measure:

 

LoadWeight Aggregate = 

	CALCULATE(
SUM('fact HCLoads'[LoadWeight]),
FILTER (
    all('fact HCLoads'[DeliveryDateTime].[date]),
            'fact HCLoads'[DeliveryDateTime].[Date] >= MIN('fact HCLoads'[DTMinus12].[Date])
           && 'fact HCLoads'[DeliveryDateTime].[DATE] <= MAX('fact HCLoads'[DTPlus12].[DATE]
)))

 

 

Load WeightDelivery DateDelivery Date -12Delivery Date + 12Rolling 24 hr sum
51/1/2020 0:1312/31/2019 12:131/1/2020 12:1310
51/1/2020 3:4712/31/2019 15:471/1/2020 15:4710
61/3/2020 1:571/2/2020 13:571/3/2020 13:576
71/5/2020 1:101/4/2020 13:101/5/2020 13:1024
81/5/2020 1:581/4/2020 13:581/5/2020 13:5824
91/5/2020 3:011/4/2020 15:011/5/2020 15:0124
71/7/2020 1:281/6/2020 13:281/7/2020 13:287

 

Dave

 

1 REPLY 1
amitchandak
Super User
Super User

@SuperFave , I have not got it completely.

But try like

	CALCULATE(
SUM('fact HCLoads'[LoadWeight]),
FILTER (
    all('fact HCLoads'[DeliveryDateTime]),
            'fact HCLoads'[DeliveryDateTime] >= MIN('fact HCLoads'[DeliveryDateTime]) -time(24,0,0)
           && 'fact HCLoads'[DeliveryDateTime] <= MAX('fact HCLoads'[DTPlus12].[DATE]
)))

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.