Help with creating double chart line to compare current FY vs prev FY

[Marshmallow]

Hi,

I am hoping there is a simple way to do this. Here is sample data.

Can someone please help me with the dax script to create double line chart with Month as X-axis and Count (date signed) with condition only count date signed that has: PA, PA1, PA2 on it. Also to hide Oct onwards for year 2025 as there is no data.

Then comes next financial year, it will automatically switch graph to show FY25-26 and FY 26-27

Date_signed	Condition
1/7/24	PA
31/7/24	PA1
15/8/24	PA2
19/9/24	AAA
30/9/24	PA1
10/10/24	PA2
12/10/24	AAA
13/10/24	PA1
12/11/24	PA2
7/12/24	PA
2/12/24	PA1
3/12/24	PA2
4/1/25	PA
31/2/25	PA1
15/3/25	PA2
19/4/25	PA
20/5/25	PA1
10/5/25	PA2
23/6/25	PA
13/7/25	PA1
12/7/25	PA2
7/8/25	PA
2/9/25	PA1
15/9/25	PA2

The chart needs to look like:

Hopefully someone able to assist as at the moment i think i have done the inefficient way.

[Ashish_Mathur]

Hi,

From which month does the FY start?

[Marshmallow]

Hi Ashish - Fin year starts from 1 July yyyy and ends on 30 June yyyy+1

[Ashish_Mathur]

PBI file attached.

[Jihwan_Kim · Accepted solution]

Hi,

I assume fiscal year starts every March.

I tried to create calendar table, and please check the below picture and the attached pbix file if it suits your inquiry.

Count: = 
CALCULATE ( COUNTROWS ( data ), data[Condition] IN { "PA", "PA1", "PA2" } )

current year: = 
VAR _currentyear =
    YEAR ( CALCULATE ( MAX ( data[Date_signed] ), REMOVEFILTERS () ) )
VAR _currentfiscalyear =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Calendar Year] = _currentyear ),
        'Calendar'[Fiscal Year]
    )
VAR _count =
    CALCULATE (
        [Count:],
        'Calendar'[Fiscal Year] = _currentfiscalyear
    )
RETURN
    _count

previous year: = 
VAR _currentyear =
    YEAR ( CALCULATE ( MAX ( data[Date_signed] ), REMOVEFILTERS () ) )
VAR _currentfiscalyearsort =
    MAXX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Calendar Year] = _currentyear ),
        'Calendar'[Fiscal Year sort]
    )
VAR _count =
    CALCULATE (
        [Count:],
        'Calendar'[Fiscal Year sort] = _currentfiscalyearsort - 1
    )
RETURN
    IF ( [current year:], _count )

[Irwan]

hello   

i would do like this (not sure if this same as yours).

create two measures for Current FY and Prev FY with following DAX

Current FY =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        YEAR('Table'[Date_signed])=YEAR(TODAY())&&
        ('Table'[Condition]="PA"||'Table'[Condition]="PA1"||'Table'[Condition]="PA2")
    )
)

Prev FY =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        YEAR('Table'[Date_signed])=YEAR(TODAY())-1&&
        ('Table'[Condition]="PA"||'Table'[Condition]="PA1"||'Table'[Condition]="PA2")
    )
)

then create another measure for display (seems you want to show when both have value).

Show =
IF(
    ISBLANK([Prev FY])||ISBLANK([Current FY]),
    0,
    1
)

  

Hope this will help.

Thank you.

@Marshmallow

[Marshmallow]

Where is the months post Sept? It needs to be displayed too as 2024 has data in it.