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) ...
  • Jihwan_Kim's avatar
    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 )