Forum Discussion

Marshmallow's avatar
Marshmallow
Icon for Helper II rankHelper II
1 year ago
Solved

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

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) ...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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's avatar
Irwan
Icon for Super User rankSuper User
1 year ago

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's avatar
Marshmallow
Icon for Helper II rankHelper II
1 year ago

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