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 I think you are too quick! I realized I had an error in the formula and edited the post to correct it. Please check again after doing a page refresh. The offending piece of code was the first MONTH function in the PreCOVIDStart variable.

latimert's avatar
latimert
Regular Visitor
3 years ago

Greg_Deckler May have been a bit too keen haha.

This measure works, however, it is extremely slow to load into a table of 6 rows (July - December). Is it expected that this would be slow? The dataset isn't huge and most measures are instantaneous. 

If you have any tips to make this faster, that would be much appreciated.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    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).

     

    • latimert's avatar
      latimert
      Regular Visitor
      3 years ago

      Greg_Deckler 

      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!

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        latimert I've got a lot of problems with that function, but it's useful when it's useful! Festivus!