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

     

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

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