Calculate intraday rolling average as fixed columns

Hi Anonymous ,

In my opinion, based on the original table, creating calculated columns for aggregating results, then creating dimension tables, then dimension tables aggregating results based on calculated columns from the original table：

start_time =
FORMAT (
    FORMAT ( 'Table'[File_Date], "" ) & " "
        & LEFT ( 'Table'[Interval_30_Minutes], 5 ),
    "dd-MM-yyyy HH:mm:ss"
)

end_time =
IF (
    LEFT ( 'Table'[Interval_30_Minutes], 5 ) = "23:30",
    FORMAT (
        FORMAT ( 'Table'[File_Date] + 1, "" ) & " "
            & RIGHT ( 'Table'[Interval_30_Minutes], 5 ),
        "dd-MM-yyyy HH:mm:ss"
    ),
    FORMAT (
        FORMAT ( 'Table'[File_Date], "" ) & " "
            & RIGHT ( 'Table'[Interval_30_Minutes], 5 ),
        "dd-MM-yyyy HH:mm:ss"
    )
)

Then use the time to compare,accord time to create flag  column(see the blow ,pd=1,then 12:00 pm):

0c =
FORMAT ( FORMAT ( 'Table'[File_Date], "" ) & " 00:00", "dd-MM-yyyy HH:mm:ss" )

pd1 = IF('Table'[start_time]>='Table'[0c]&&'Table'[end_time]<='Table'[12c],1,0)

 

 

Enter another table:

AND 12:00 use the below dax:("abandon %"and "Avg time to abandon "  you not provide how to get it,so I not calculate it  )

12:00 PM =
VAR In_SLA =
    CALCULATE (
        SUM ( 'Table'[In_SLA] ),
        FILTER ( ALL ( 'Table' ), 'Table'[pd1] = 1 )
    )
VAR out_sla =
    CALCULATE (
        SUM ( 'Table'[Out_SLA] ),
        FILTER ( ALL ( 'Table' ), 'Table'[pd1] = 1 )
    )
VAR test1 =
    SWITCH (
        Table2[type],
        "Offered calls",
            CALCULATE (
                SUM ( 'Table'[Offered] ),
                FILTER ( ALL ( 'Table' ), 'Table'[pd1] = 1 )
            ),
        "Handle calls",
            CALCULATE (
                SUM ( 'Table'[Handled] ),
                FILTER ( ALL ( 'Table' ), 'Table'[pd1] = 1 )
            ),
        "Abandon calls",
            CALCULATE (
                SUM ( 'Table'[Abandons] ),
                FILTER ( ALL ( 'Table' ), 'Table'[pd1] = 1 )
            ),
        "sl %", DIVIDE ( In_SLA, In_SLA + out_sla, 4 ),
        BLANK ()
    )
RETURN
    test1

 

Out put:

 

And the data your provided get result is not match the result picture value.

I provided my pbix file if you need.

 

 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

Hi Anonymous ,

 

Sorry for the delay on the answer had some work issues.

 

I believe that this can be achieved using the following approach:

• Add a custom column with the end time

• Add a new table with the following setup:

• Add the following measure to your model (missing only the abadon time measure):

 

Offered calls = CALCULATE(SUM(Calls[Offered]),Calls[End Time] <= MAX(Times[End Time]))

Handled calls = CALCULATE(SUM(Calls[Handled]),Calls[End Time] <= MAX(Times[End Time]))

Abandon calls = CALCULATE(SUM(Calls[Abandons]),Calls[End Time] <= MAX(Times[End Time]))

Abandon % = DIVIDE( [Abandon calls],[Offered calls])

SL % =
DIVIDE (
    CALCULATE ( SUM ( Calls[In_SLA] ), Calls[End Time] <= MAX ( Times[End Time] ) ),
    (
        CALCULATE ( SUM ( Calls[In_SLA] ), Calls[End Time] <= MAX ( Times[End Time] ) )
            + CALCULATE ( SUM ( Calls[Out_SLA] ), Calls[End Time] <= MAX ( Times[End Time] ) )
    )
)

 

 

• Create a matrix with the following setup:
  • Column: Times[Time]
  • Values:
    • All the measures created above

Result below and in attach PBIX file.