Calculate intraday rolling average as fixed columns

[Anonymous]

I am trying to create measure which will calculate periodically based on the 30-min interval. 

In the below pic my SL% is In_SLA/(In_SLA+Out_SLA) from data.

columns should be calculated based on below rules:

12:00 PM = 30_min_interval 00:00 - 12:00   

04:00 PM = 30_min_interval  00:00 - 16:00

08:00 PM = 30_min_interval  00:00 - 20:00

EOD = 30_min_interval  00:00 - 00:00

Based on this data, can someone please suggest what DAX should I use to acheive above result.

File_Date	Interval_30_Minutes	Offered	Handled	In_SLA	Out_SLA	Abandons
11/11/2021	09:00-09:30	238	187	21	217	51
11/11/2021	09:00-09:30	3	3	2	1
11/11/2021	09:30-10:00	189	159	12	177	30
11/11/2021	09:30-10:00	2	2	2
11/11/2021	10:00-10:30	234	231	231	3	3
11/11/2021	10:00-10:30	5	5	5
11/11/2021	10:30-11:00	223	220	220	3	3
11/11/2021	10:30-11:00	3	2	2	1	1
11/11/2021	11:00-11:30	408	393	393	15	15
11/11/2021	11:00-11:30	11	10	7	4	1
11/11/2021	11:30-12:00	433	428	427	6	5
11/11/2021	11:30-12:00	16	12	10	6	4
11/11/2021	12:00-12:30	523	518	517	6	5
11/11/2021	12:00-12:30	27	25	14	13	2
11/11/2021	12:30-13:00	419	416	414	5	3
11/11/2021	12:30-13:00	21	21	12	9
11/11/2021	13:00-13:30	419	408	407	12	11
11/11/2021	13:00-13:30	28	28	19	9
11/11/2021	15:30-16:00	421	409	410	11	11
11/11/2021	15:30-16:00	17	17	14	3
11/11/2021	19:30-20:00	313	308	308	5	5
11/11/2021	19:30-20:00	16	16	14	2
11/11/2021	22:30-23:00	80	77	77	3	3
11/11/2021	22:30-23:00	2	2	2
11/11/2021	23:00 - 23:30	31	30	26	5	1
11/11/2021	23:30-00:00	16	12	10	6	4

[v-luwang-msft · Accepted solution]

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

[v-luwang-msft · Accepted solution]

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

[MFelix]

Hi @Anonymous ,

Is the result on top based on the table below? I cannot reach the same values has you can you please explain a little better how the calculations are being made especially for the percentages?

For example you refer that the firs values are from 0 to 12 if I sum up the columns I get the following resuts:

Offered - 1765

Handled - 1652

Abandon - 113

Can you please confirm?

[Anonymous]

Hi @MFelix , thanks for taking interest in my post.

values in picture are dummy values and are not accurate. I just want to know how we can form the custom columns using DAX or any other process to acheive the result similar to that image. 

I pasted only few rows of my actual data here for representation. Apologize if it creates any confusion

[MFelix · Accepted solution]

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.