Similar period with variable year interval

[latimert]

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.

[latimert]

@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:

Year	Month	Current Year	Pre COVID
2022	Aug	$        1,000,000	$  2,000,000
2022	Sep	$        1,000,000	$  2,000,000
2022	Oct	$        1,000,000	$  2,000,000
2022	Nov	$        1,000,000	$  2,000,000
2022	Dec	$        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]

@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]

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]

@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]

@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 · Accepted solution]

@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]

@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]

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

[Greg_Deckler]

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