Forum Discussion
Variation of count - between Fiscal Years
Hello Everyone, I am struggling to calculate a variation of count between fiscal years.
1- I created my fiscal year column using this formula.
_FiscalYearNumber_ = if(MONTH('MyTable'[date_column]) <= 9, VALUE(FORMAT('MyTable'[date_column],"YYYY")),VALUE(FORMAT('MyTable'[date_column],"YYYY"))+1)
_FiscalYearText_ = "FY "&Right(Format('MyTable'[_FiscalYearNumber_],"0#")-1,2)&"-"&Right(FORMAT('MyTable'[_FiscalYearNumber_],"0#"),2)I used the FiscalYearText in the Axis of my clustered column chart
2- Using the _FiscalYearNumber_, I can calculate the count for the Actual Fiscal Year... like this (a new measure):
_card_count_A-FY_ = CALCULATE(count('MyTable'[card]),'MyTable'[_FiscalYearNumber_])Now this is where I am stucked...
How to calculate the Last Fiscal Year: _card_count_L-FY_, so I can make a difference be the two (_card_count_A-FY_ and _card_count_L-FY_) and DIVIDE([_card_count_VAR_FY],[_card_count_L-FY_]) to have the VAR %...
Thank you for your help and insights!
I used this formula... but it does not work
_card_count_L-FY_ = CALCULATE([_card_count_A-FY_],'Mytable'[_FiscalYearNumber_]-1)
Hi,
I still do not know what you want but i have attempted something. Download my PBI file from here.
Hope this helps.
5 Replies
- Ashish_Mathur
Super User
Hi,
It will help if you share the link from where i can download your PBI file and also show the expected result there.
- HeybeeFrequent Visitor
Ashish_Mathur wrote:Hi,
It will help if you share the link from where i can download your PBI file and also show the expected result there.
Hi!
Please, find the file here: PBI file
The expected result is the variation graph between Fiscal Years.
Hope it is clear.. thank you again!
Regards,
- Ashish_Mathur
Super User
Hi,
I still do not know what you want but i have attempted something. Download my PBI file from here.
Hope this helps.