Forum Discussion

latimert's avatar
latimert
Regular Visitor
3 years ago
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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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
  __SalesPC

    Filtering 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).

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

latimert Try something along the lines of:

Measure = 
  VAR __Year = YEAR(TODAY())
  VAR __PreCOVIDStart = DATE(2019,3,1)
  VAR __PreCOVIDEnd = DATE(2020,2,29)
  VAR __SalesTY = SUMX(FILTER(ALL('Table'),YEAR('Table'[Date]) = __Year),[Sales])
  VAR __SalesPC = SUMX(FILTER(ALL('Table'),'Table'[Date]>= __PreCOVIDStart && [Date]<=__PreCOVIDEnd),[Sales])
RETURN
  DIVIDE(__SalesTY,__SalesPC,0)