Last 6 months as at dax measure for actions

[Anonymous]

Hi everyone, I have an actions table which has raised date, closed date, duedate and status fields. I need to create a visual that gives for the last 6months within SLA, overdue, 30 plus days overdue actions as at the end of the month. Within SLA actions are actions that were open as at the end of the month but not past their due date, overdue actions are actions that were open but past their due date, 30 plus overdue actions were overdue actions that were more than 30 days past their due date. Open actions definition is raised by the end of the month and status open OR status = closed, raised date before the end of the month and date closed is after the end of the month. At present the chart is generated by a SQL view which is not ideal as it does not allow slicing and dicing the visual. I would like to know if I can create a measure that would give me within SLA, overdue and 30 plus days overdue as at the end of the month based on the above conditions for the past 6 months? Thank you.

[Anonymous]

Hi, as suggested here is the dummy actions data and desired output table for PBI visualisation. I need to make it a dynamic date range (ie rolling 6 months for 4 metrics listed). I have experimented with dates table joins to the raw actions table and various DAX measures based on these inactive relationships but to no avail. Again any help would be greatly appreciated.

Dummy raw data

ActionID	CUrrentStatus	DateRaised	DateClosed	DUeDate
1	Closed	16/03/2020	19/05/2020	13/08/2020
2	Open	24/01/2020		05/11/2020
3	Open	07/03/2020		31/12/2020
4	Closed	23/03/2020	31/05/2020	19/12/2020
5	Open	17/01/2020		02/07/2020
6	Closed	26/02/2020	26/04/2020	05/04/2020
7	Open	30/03/2020		08/12/2020
8	Closed	23/06/2020	29/07/2020	01/10/2020
9	Open	14/01/2020		20/12/2020
10	Closed	17/02/2020	13/06/2020	31/12/2020
11	Closed	18/06/2020	19/06/2020	03/08/2020
12	Closed	25/04/2020	14/05/2020	16/08/2020
13	Closed	13/01/2020	21/04/2020	09/12/2020
14	Closed	04/03/2020	28/03/2020	06/06/2020
15	Closed	18/02/2020	26/04/2020	21/02/2020
16	Closed	19/01/2020	25/05/2020	14/07/2020
17	Open	07/02/2020		03/11/2020
18	Open	07/05/2020		18/10/2020
19	Closed	28/06/2020	05/07/2020	19/09/2020
20	Closed	22/05/2020	01/06/2020	16/07/2020
21	Closed	23/04/2020	31/05/2020	09/07/2020
22	Closed	15/01/2020	11/05/2020	26/02/2020
23	Open	21/06/2020		01/09/2020
24	Open	06/02/2020		18/12/2020
25	Closed	17/04/2020	05/06/2020	11/11/2020
26	Closed	14/06/2020	20/06/2020	11/11/2020
27	Closed	08/06/2020	27/06/2020	12/12/2020
28	Open	05/03/2020		22/04/2020
29	Closed	25/04/2020	17/05/2020	05/06/2020
30	Open	29/04/2020		30/08/2020

Desired output table for visualisation

	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20
Raised	6	5	6	5	2	6
Within SLA	6	9	14	16	12	12
Overdue	0	2	2	2	1	1
Overdue 30 plus	0	0	2	1	1	1

[v-shex-msft]

Hi @Anonymous,

I try to create two calculated tables with date values and categories that used on matrix and write a measure formula to summary results based on the matrix category and data ranges from the raw table. You can try it if it meets your requirement.

Tables:

Category = 
DATATABLE (
    "Type", STRING,
    "Index", INTEGER,
    {
        { "Raised", 1 },
        { "Within SLA", 2 },
        { "Overdue", 3 },
        { "Overdue 30 plus", 4 }
    }
)

dateTable = 
VAR list =
    UNION (
        ALL ( 'Table'[DateClosed] ),
        ALL ( 'Table'[DateRaised] ),
        ALL ( 'Table'[DUeDate] )
    )
RETURN
    CALENDAR ( MINX ( list, [DateClosed] ), MAXX ( list, [DateClosed] ) )

Measure:

Measure = 
VAR currDate =
    MAX ( dateTable[Date] )
RETURN
    SWITCH (
        SELECTEDVALUE ( Category[Type] ),
        "Raised",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] IN VALUES ( dateTable[Date] )
                        && OR ( [DateClosed] > currDate, [DateClosed] = BLANK () )
                )
            ),
        "Within SLA",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] IN VALUES ( dateTable[Date] )
                        && [DateClosed] <= [DUeDate]
                )
            ),
        "Overdue",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] IN VALUES ( dateTable[Date] )
                        && OR ( [DateClosed] > [DUeDate], [DateClosed] = BLANK () )
                )
            ),
        "Overdue 30 plus",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] IN VALUES ( dateTable[Date] )
                        && OR ( [DateClosed] > [DUeDate], [DateClosed] = BLANK () )
                        && DATEDIFF (
                            [DUeDate],
                            MIN ( [DateClosed], MAXX ( ALL ( dateTable[Date] ), [Date] ) ),
                            DAY
                        ) >= 30
                )
            )
    ) + 0

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin, thanks for your work on this but this is not exactly what I am looking for. Within SLA, Overdue and Overdue 30 plus measures should be up to and including the reporting month which means they should be cumulative). So for Feb for example I need to know how many actions raised up to the end of Feb were within SLA as at the end of Feb. This includes now closed actions that were open at the end of Feb (determined based on the action closed date).

[v-shex-msft]

Hi @Anonymous ,

Maybe you can try this one, now the formula will rolling on the date range(include previous records) based on the current date:

Measure 2 = 
VAR currDate =
    MAX ( dateTable[Date] )
RETURN
    SWITCH (
        SELECTEDVALUE ( Category[Type] ),
        "Raised",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] IN VALUES ( dateTable[Date] )
                        && OR ( [DateClosed] > currDate, [DateClosed] = BLANK () )
                )
            ),
        "Within SLA",
            COUNTROWS (
                FILTER ( 'Table', [DateRaised] <= currDate && [DateClosed] <= [DUeDate] )
            ),
        "Overdue",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] <= currDate
                        && OR ( [DateClosed] > [DUeDate], [DateClosed] = BLANK () )
                )
            ),
        "Overdue 30 plus",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] <= currDate
                        && OR ( [DateClosed] > [DUeDate], [DateClosed] = BLANK () )
                        && DATEDIFF (
                            [DUeDate],
                            MIN ( [DateClosed], MAXX ( ALL ( dateTable[Date] ), [Date] ) ),
                            DAY
                        ) >= 30
                )
            )
    )

Regards,

Xiaxoin Sheng

[Anonymous · Accepted solution]

Hi @v-shex-msft

I have managed to work it out with your help. The solution involved using the DAX measure below. The table in Power BI now matches the desired output table I posted previously. Thanks again for your support.

Measure 3 = 
VAR currDate =
    MAX ( dateTable[Date] )
RETURN
    SWITCH (
        SELECTEDVALUE ( Category[Type] ),
        "Raised",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] IN VALUES ( dateTable[Date] )
                 )
                )
            ,
        "Within SLA",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] <= currDate
                        && OR([CUrrentStatus] = "Open",[CUrrentStatus]="Closed"&&[DateClosed]>CurrDate) && [DUeDate]>= currDate)
                ),
        "Overdue",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] <= currDate
                        && OR([CUrrentStatus] = "Open",[CUrrentStatus]="Closed"&&[DateClosed]>CurrDate) && [DUeDate]< currDate)
                ),
        "Overdue 30 plus",
            COUNTROWS (
                FILTER (
                    'Table',
                    [DateRaised] <= currDate
                        && OR([CUrrentStatus] = "Open",[CUrrentStatus]="Closed"&&[DateClosed]>CurrDate) && [DUeDate]< currDate && DATEDIFF('Table'[DUeDate],currDate,DAY)>=30)
                )
    )

[v-shex-msft]

Hi @Anonymous,

Please share some dummy data and expected results then we can test to coding formula on it.

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , In this You need a date table joined with all dates and One join will active and others are inactive.  You can make join active using userelation.

Refer https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

The best to display 6 months is relative date slicer :https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

if not then follow this approch :https://www.youtube.com/watch?v=duMSovyosXE

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions


Appreciate your Kudos.

[VijayP]

@Anonymous 

Let me know whether this Video helps you https://www.youtube.com/watch?v=3bV9BfB3_Oc&t=1s , please share your kudoes

Vijay Perepa