cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calculate intraday rolling average as fixed columns

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

Dheerajy_0-1636771441252.png

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

 

File_DateInterval_30_MinutesOfferedHandledIn_SLAOut_SLAAbandons
11/11/202109:00-09:302381872121751
11/11/202109:00-09:303321 
11/11/202109:30-10:001891591217730
11/11/202109:30-10:00222  
11/11/202110:00-10:3023423123133
11/11/202110:00-10:30555  
11/11/202110:30-11:0022322022033
11/11/202110:30-11:0032211
11/11/202111:00-11:304083933931515
11/11/202111:00-11:301110741
11/11/202111:30-12:0043342842765
11/11/202111:30-12:0016121064
11/11/202112:00-12:3052351851765
11/11/202112:00-12:30272514132
11/11/202112:30-13:0041941641453
11/11/202112:30-13:002121129 
11/11/202113:00-13:304194084071211
11/11/202113:00-13:302828199 
11/11/202115:30-16:004214094101111
11/11/202115:30-16:001717143 
11/11/202119:30-20:0031330830855
11/11/202119:30-20:001616142 
11/11/202122:30-23:0080777733
11/11/202122:30-23:00222  
11/11/202123:00 - 23:3031302651
11/11/202123:30-00:0016121064
2 ACCEPTED SOLUTIONS
v-luwang-msft
Community Support
Community Support

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)

 

vluwangmsft_0-1637228868857.png

 

Enter another table:

vluwangmsft_1-1637228926091.png

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:

vluwangmsft_2-1637229055397.png

 

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

View solution in original post

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

MFelix_0-1637230523716.png

  • Add a new table with the following setup:

MFelix_1-1637230560741.png

  • 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.

MFelix_2-1637230746031.png

 

 

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

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)

 

vluwangmsft_0-1637228868857.png

 

Enter another table:

vluwangmsft_1-1637228926091.png

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:

vluwangmsft_2-1637229055397.png

 

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
Super User
Super User

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?

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

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

MFelix_0-1637230523716.png

  • Add a new table with the following setup:

MFelix_1-1637230560741.png

  • 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.

MFelix_2-1637230746031.png

 

 

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!