Forum Discussion
Helper IIHelp 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) 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.
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 )
6 Replies
- Jihwan_Kim
Super User
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 ) 
Irwanhello
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.
MarshmallowWhere is the months post Sept? It needs to be displayed too as 2024 has data in it.
- 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.
