Forum Discussion
Similar period with variable year interval
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).
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!