Forum Discussion
Solved
Calculate function with a filter based on another measures's value- fiscal month
Hello, our company is using fiscal months. The length of these periods are set up randomly without any kind of logic. This is how the past year looks like: 5-4-4-4-5-4-4-5-4-5-4-4 weeks in each month...
- This formula below worked as a solution. Where FiscalMonthYearNum is month number+year (42018 for April 2018). And PreviousFiscalMonthYearNum = FiscalMonthYearNum-1 (42017 for April 2017) Revenue Prior Year = CALCULATE([Revenue], FILTER ( ALL ( 'Calendar - new' ), CONTAINS ( VALUES ( 'Calendar - new'[PreviousFiscalMonthYearNum] ), 'Calendar - new'[PreviousFiscalMonthYearNum], 'Calendar - new'[FiscalMonthYearNum] ) ) )
Unfortunately, this formula returns incorrect amounts.. Last period should somehow be controlled by the FiscalYearMonth column
This formula below worked as a solution. Where FiscalMonthYearNum is month number+year (42018 for April 2018). And PreviousFiscalMonthYearNum = FiscalMonthYearNum-1 (42017 for April 2017) Revenue Prior Year = CALCULATE([Revenue], FILTER ( ALL ( 'Calendar - new' ), CONTAINS ( VALUES ( 'Calendar - new'[PreviousFiscalMonthYearNum] ), 'Calendar - new'[PreviousFiscalMonthYearNum], 'Calendar - new'[FiscalMonthYearNum] ) ) )