Forum Discussion
Marshmallow
Helper II
1 year agoHelp 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) ...
- 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 _countprevious 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 )
Jihwan_Kim
Super User
1 year agoHi,
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 )