Help to create double line chart for YOY comparison with conditions from multiple columns

[Marshmallow]

Hi,

Can someone please help me with the DAX as I can't seem to get it right? I need to create a stacked line charts for YOY to show total rows of PA_Date that is not BLANK (01/01/00) per Month in the Financial Year (starting from July) and column 'RejectionDate is BLANK (01/01/00). Hopefully this makes sense.

Below is my sample data:

RejectionDate	PA_Date
01/01/00	01/01/00
01/01/00	01/01/00
01/01/00	01/01/00
01/01/00	01/01/00
16/12/24	01/01/00
01/01/00	01/01/00
01/01/00	14/08/24
01/01/00	13/08/23
01/01/00	30/09/24
01/01/00	03/06/24
01/01/00	17/08/23
29/01/24	02/07/23
01/01/00	12/04/24
01/01/00	03/09/24
01/01/00	22/12/22
01/01/00	12/04/24
01/04/25	01/04/25
01/01/00	06/12/24
09/05/25	09/05/25
19/02/25	18/02/25
28/05/24	01/01/00
14/01/25	04/09/24
27/02/25	27/02/25
21/05/25	30/09/24
01/01/00	02/07/24
01/01/00	10/09/23
01/01/00	30/07/24
01/01/00	11/11/24
30/01/24	01/11/22
01/01/00	22/07/25
09/06/25	09/06/25
01/05/24	09/11/23
13/06/25	13/06/25
01/01/00	10/07/25
01/01/00	12/06/25
06/08/24	01/05/24
01/01/00	20/08/24
01/01/00	17/06/25
01/01/00	23/08/24
08/04/24	05/08/21
01/01/00	14/05/24
01/01/00	08/06/22
01/01/00	26/11/24
01/01/00	05/12/24
14/02/24	29/08/22
01/01/00	29/07/24
01/01/00	18/06/25
01/01/00	02/07/24
01/01/00	18/06/25
01/01/00	01/01/00
01/01/00	28/02/25
01/01/00	04/07/23
26/02/25	01/01/00
25/06/25	24/06/25
01/01/00	01/04/25
11/09/24	04/09/24
01/01/00	31/07/23
01/01/00	20/09/24
01/01/00	25/06/24
01/01/00	02/07/23
01/01/00	31/03/25
01/01/00	30/09/24
01/01/00	14/10/24
01/01/00	18/02/25
01/01/00	11/07/24
01/01/00	05/12/24
01/01/00	08/11/24
01/01/00	06/11/24
01/01/00	19/09/24
01/01/00	04/11/24
01/01/00	01/01/00
01/01/00	15/07/25
01/01/00	06/02/25
01/01/00	04/09/25
01/01/00	03/06/25
01/01/00	11/07/25
26/05/25	30/09/24
01/07/25	18/02/25
01/01/00	30/09/24
01/01/00	24/09/24
14/11/24	16/05/24
01/01/00	26/05/25
01/01/00	09/09/25
01/01/00	30/09/24
01/01/00	08/07/25
01/01/00	01/01/00
25/03/25	24/03/25
01/01/00	28/02/25
01/01/00	25/08/25

I need to be able to create a stacked line chart like the sample below - with no luck. (ps: ignore the vertical line)

What I have done so far:

1. I created a calendar table

Calendar_New =

VAR _calendar =

    ADDCOLUMNS (

        CALENDAR (

            DATE ( YEAR ( MIN (data ) ), 1, 1 ),

            DATE ( YEAR ( MAX ( data ) ), 12, 31 )

        ),

        "Calendar Year", YEAR ( [Date] ),

        "Month name", FORMAT ( [Date], "mmmm" ),

        "Month number", MONTH ( [Date] )

    )

RETURN

    ADDCOLUMNS (

        _calendar,

        "Financial Year",

            IF (

                [Month number] >= 7,

                "FY" & FORMAT ( [Date], "yy" ) & "-"

                    & FORMAT ( [Date], "yy" ) + 1,

                "FY"

                    & FORMAT ( [Date], "yy" ) - 1 & "-"

                    & FORMAT ( [Date], "yy" )

            ),

        "Financial Year sort",

            IF ( [Month number] >= 7, [Calendar Year] + 1, [Calendar Year]),

        "Financial Month number",

            IF ( [Month number] >= 7, [Month number] - 2, [Month number] + 12 - 2 ),

        "Month name index",

            IF ([Month number] >= 7, [Month number]-6, [Month number] +6)

    )

2. Do total count which i think there is an issue here

Count_PA =

CALCULATE(

    COUNTROWS(data),

    Not(ISBLANK(data[RejectionDate])))

After this step my other calculations have returned incorrect numbers -- step 3 and 4 below -- 😞

3. 

Current Yr ProvAppr =

var _currentyear =

    year(CALCULATE(

        max(data[PA_Date]),

         Not ISBLANK(data[RejectionDate]), REMOVEFILTERS() ))

var _currentFY=

    MAXX(

        filter(All(Calendar_New), 'Calendar_New'[Calendar Year] = _currentyear),

        'Calendar_New'[Financial Year])

var _count =

    CALCULATE(

        [Count_ProvAppr],

        'Calendar_New'[Financial Year] = _currentFY)

        RETURN

        _count

4. 

Previous Yr ProvAppr=

var _currentyear = year(CALCULATE(max(data[PA_Date]), Not ISBLANK(data[RejectionDate]) REMOVEFILTERS() ) )

var _currentFYsort =

    MAXX(

        Filter(ALL('Calendar_New'), 'Calendar_New'[Calendar Year] = _currentyear),

        'Calendar_New'[Financial Year sort])

var _count =

    CALCULATE([Count_ProvAppr],

     'Calendar_New'[Financial Year sort] =  _currentFYsort-1)

RETURN

    if([Current Yr ProvAppr], _count)

These are the steps I did and I can't get my total numbers right :(. 

Pls help. 

[MFelix · Accepted solution]

Hi @Marshmallow ,

Based on your description I believe that your main problem is the measure:

Count_PA =
CALCULATE(
    COUNTROWS(data),
    Not(ISBLANK(data[RejectionDate])))

This calculation gets the values of the Data were the Rejection date is not blank you refer that you want to have the ones where the rejection date is blank so try the following code:

Count_PA = 
    COUNTROWS(FILTER(data,
    data[RejectionDate] = BLANK()))

Then setup your chart with the following:

• X-Axis - Month Name
• Y-Axis - Count_PA
• Legend: Finacial Year

Result below: