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 pre-COVID period where COVID hadn't affected the business significantly.

 

So I'd need a measure to compare sales from Mar-Dec this year with Mar-Dec 19, and Jan-Feb this year with Jan-Feb 20.

 

I believe the measure will look something like CALCULATE(SUM('Table1'[Actuals]), filter to compare with same period 2019 or 2020)

I've had a look into the PARALLELPERIOD function but I had trouble with using a variable for the interval argument.

 

Please let me know if this makes sense or if any further information is required.

Thanks in advance.

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

     

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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)
  • latimert's avatar
    latimert
    Regular Visitor
    3 years ago

    Greg_Deckler 

     

    Thanks for your reply.

    Looks like it will work for overalll, but I'm wondering if it's possible to get this working to display something like the following:

    YearMonthCurrent YearPre COVID
    2022Aug $        1,000,000 $  2,000,000
    2022Sep $        1,000,000 $  2,000,000
    2022Oct $        1,000,000 $  2,000,000
    2022Nov $        1,000,000 $  2,000,000
    2022Dec $        1,000,000 $  2,000,000

     

    Ideally, I'd like to see the results side by side as in the above table.

     

    Appreciate your help.

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      latimert Ah, by month, that would be:

       

       

      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 = SUMX(FILTER(ALL('Table'),'Table'[Date]>= __PreCOVIDStart && [Date]<=__PreCOVIDEnd),[Sales])
RETURN
  __SalesPC

       

       

      • latimert's avatar
        latimert
        Regular Visitor
        3 years ago

        Hi Greg_Deckler 

         

        I get the following error when trying to add this measure:
        A single value for column 'Date' in table 'GeneralLedger' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

         

        My table is called GeneralLedger and my date column is called Date.

         

        Thanks again for your help.