Forum Discussion
Helper IIICounts based on shifted year
I work with a fiscal year calendar that starts on October 1st and ends on September 30th, so for example, fiscal year 2023 starts on October 2022 and ends on September 2023.
I need to count # of cases in Fiscal 2023 and then compare it to fiscal 2022 in a KPI card. This is what I have right now, what I do not understand is how to count based on a fiscal year without a filter, so it continues to roll over automatically, year over year. Can someone please help me?
Sample Data.pbix
Hi! I suggest using the date table I discuss in my blog from Marco Russo at SQLBI, it is meant to handle a standard calendar, fiscal, or fiscal weekly. Once you get the date table set up to work with your fiscal needs, you can just create your count measure which will serve as your this year amount such as Case Count =DISTINCTCOUNTNOBLANKS('Fact Table'[Cases]), and assuming you are filtered to a specific FY in the filter pane or with a slicer, then make a measure for last year = CALCULATE([Case Count], SAMPERIODLASTYEAR('Date'[Date])). Since your filter or slicer is set to one specific year, the prior year will work.
http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/
6 Replies
- audreygerred
Super User
Hi! I suggest using the date table I discuss in my blog from Marco Russo at SQLBI, it is meant to handle a standard calendar, fiscal, or fiscal weekly. Once you get the date table set up to work with your fiscal needs, you can just create your count measure which will serve as your this year amount such as Case Count =DISTINCTCOUNTNOBLANKS('Fact Table'[Cases]), and assuming you are filtered to a specific FY in the filter pane or with a slicer, then make a measure for last year = CALCULATE([Case Count], SAMPERIODLASTYEAR('Date'[Date])). Since your filter or slicer is set to one specific year, the prior year will work.
http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/
UnknowncharacteSo I did this:
Fiscal 2023 Count = CALCULATE([Current Year Count], 'Dim Calendar'[Fiscal Year] IN { "2023" })But rather than manually filetring, how can adjust the measure to know current fiscal year and last fiscal year, that way, it updates automatically when we are in a new year?- audreygerred
I would still suggest a filter, but set it to relative date and say is in this year, that way when the year rolls over it'll still work.
- Unknowncharacte
But will that work in a KPI? Snapshot of what I am after, bottom KPI card with comparison of previous to current year counts.
- audreygerred
Yes, I have it here showing the filter, showing a new card viz, and the kpi viz:
- Unknowncharacte
I found the answer: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Calculate-previous-fiscal-year/m-p/1578237?fbclid=IwAR24nOjhuiQAhIanh-2PI5U993gIUYb_uQ_6DgmQW86hGSwd5tfy9Tr8lbY
Case Count Current FY = CALCULATE(count('Fact Table'[IemCaseNumber]),filter(ALL('DIM Calendar'),'DIM Calendar'[Fiscal Year]=max('DIM Calendar'[Fiscal Year])))
Case Count Previous FY = CALCULATE(count('Fact Table'[IemCaseNumber]),filter(ALL('DIM Calendar'),'DIM Calendar'[Fiscal Year]=max('DIM Calendar'[Fiscal Year])-1))