I hope I can find some help to a problem that has been vexing me for the last few hours.
I have a simple sales and date table combined and I want to create a baseline from the sales table to do some scenario planning on.
The data starts from April 2018 and the Fiscal year runs from April to March.
I created measures to segment the income into different channels using a simple filter.
I then created a measure for going back two years using date add to create my baseline. The logic for this is that my scenario starts from April 2021 and I wanted a stable year to model from. If I took last years data i.e 2020 it has been a little different from the norm because of coranavirus!
This means that I now have baseline data starting from April 20 onwards (Up to Jul 22 as I write this).
I wanted to reduce the dataset to a single year so I filtered my baseline on FY as follows
Income Baseline = CALCULATE(
[Income L2Y £k], FILTER(Dates,Dates[FY] = “FY22”))
This has worked fine and given me 12 months of data in April 2021 to March 2022. The annoying thing is that March 2022 is not a great number as it was impacted by the start of coranavirus. I have therefore tried to adjust the baseline to start on 1st March 2021 and end 28th Feb 2022 (Equiv of using March 19-Feb 19).
Adjusted Income Baseline = CALCULATE(
[Income L2Y £k],
When I do this I get no data in my visualisation (no errors though). I definitely have data in those time periods. I have tried using a filter with greater than and less than and no luck there. I don’t understand how it calculates with a single value like fiscal year but not with date between.
Can anyone provide pointers on where I am going wrong here?