How to summarize table for 12 hour data on every hour

[SimonLu]

Hi, There

  i like to seeking help for below funciton :

  We have a talbe to record case data , i like to summarize those data group by Case class; Grade on previous 12 hour interval , and calculate evey hours.

Data Sample：

Result Sample

[Sandip_Palit · Accepted solution]

This single DAX formula will create the entire summary table you need.

• In Power BI Desktop, navigate to the Data view or Report view.
• From the Modeling tab on the ribbon, click New Table.
• Paste the following DAX formula into the formula bar. Be sure to replace 'Source Data' with the actual name of your data table.

Result Table =
// This formula generates a summary for every hour, calculating the sum of weight
// for each Case class and Grade over the preceding 12-hour window.

// Step 1: Create a table of every unique combination of Case class and Grade.
VAR GroupingKeys =
SUMMARIZE ( 'Source Data', 'Source Data'[Case class], 'Source Data'[Grade] )

// Step 2: Create a table containing every single hour between the first and last transaction.
VAR MinDateTime =
MIN ( 'Source Data'[Red Data] )
VAR MaxDateTime =
MAX ( 'Source Data'[Red Data] )
VAR HourlyTimePoints =
GENERATESERIES (
DATE ( YEAR ( MinDateTime ), MONTH ( MinDateTime ), DAY ( MinDateTime ) )
+ TIME ( HOUR ( MinDateTime ), 0, 0 ),
MaxDateTime,
TIME ( 1, 0, 0 )
)
VAR HourlyBins =
SELECTCOLUMNS ( HourlyTimePoints, "End date", [Value] )

// Step 3: Create the final table structure by combining every hourly point with every group.
VAR FinalTableShell =
CROSSJOIN ( HourlyBins, GroupingKeys )

// Step 4: Calculate the rolling sum for each row and format the output.
RETURN
SELECTCOLUMNS (
FILTER (
ADDCOLUMNS (
FinalTableShell,
"Weight",
VAR EndWindow = [End date]
VAR StartWindow = EndWindow - TIME ( 12, 0, 0 )
RETURN
CALCULATE (
SUM ( 'Source Data'[Weight (Net)] ),
KEEPFILTERS ( 'Source Data'[Case class] = EARLIER ( [Case class] ) ),
KEEPFILTERS ( 'Source Data'[Grade] = EARLIER ( [Grade] ) ),
'Source Data'[Red Data] > StartWindow
&& 'Source Data'[Red Data] <= EndWindow
)
),
[Weight] > 0
),
"Date", FORMAT ( [End date], "M/d/yyyy h:mm" ),
"Start Date", [End date] - TIME ( 12, 0, 0 ),
"End date", [End date],
"Case class", [Case class],
"Grade", [Grade],
"Weight", "Sum(Weight)" -- This is just a text label as in your example
)

If this explanation and solution resolve your issue, please like and accept the solution.

[SimonLu]

Thanks, its working perfect.