Forum Discussion
Solved
Similar period with variable year interval
Hello, I'm looking for a way to compare current year sales results with results from Mar-19 to Feb-20. We use this to measure against impacts of COVID etc and we consider Mar-19 to Feb-20 our pr...
latimert Well, you can try this:
Measure = VAR __Start = MIN('Table'[Date]) VAR __PreCOVIDStart = IF(MONTH(__Start) > 2, DATE(2019,MONTH(__Start),1), DATE(2020,MONTH(__Start),1) ) VAR __PreCOVIDEnd = EOMONTH(__PreCOVIDStart,0) VAR __SalesPC = CALCULATE(SUM('Table'[Sales]),ALL('Table'),'Table'[Date]>= __PreCOVIDStart && [Date]<=__PreCOVIDEnd) RETURN __SalesPCFiltering ranges sucks and using CALCULATE is hit or miss depending on your visual configuration and whether or not you have a star schema as well as what cycle the moon happens to be in (generally works during a Waning Gibbous).
Will just tell the recipient to wait for the next waning gibbous then!
Jokes aside, calculate was significantly faster but I'll keep an eye out for any inconsistencies.
This has been very helpful. Thank you very much!
latimert I've got a lot of problems with that function, but it's useful when it's useful! Festivus!