PLZ HLP!!!! Creating calculation for Comparing Same Date This Year versus Last Year

[TheSweeper]

I am trying to do Time Series calculations (whatever that really means) for Same Date compared to Same Date Last Year. If you can kindly help me from  losing more hair I would kindly appreciate your generosity!  

Here are the formulas I am using 

• Collections LY by trend date = CALCULATE([Collections Total],SAMEPERIODLASTYEAR(('Calendar'[Date]))) - This is still showing dates until end of year.  I only want it to show data up until this years date last year.  
• Collections TY by trend date = CALCULATE([Collections Total], FILTER('Calendar', 'Calendar'[Date] < TODAY()))

  

  Date	Collections TY by trend date	Collections LY by trend date
  11/30/2021 0:00		$34,776.25
  11/28/2021 0:00		$852.91
  11/27/2021 0:00		$31,860.07
  11/26/2021 0:00		$207.88
  11/25/2021 0:00		$52,373.37
  11/24/2021 0:00		$14,278.67
  11/23/2021 0:00		$31,774.04
  11/22/2021 0:00	$24,062
  11/20/2021 0:00		$69,379.20
  11/19/2021 0:00	$12,817.15	$32,289.16
  11/18/2021 0:00	$11,018.94	$16,537.48
  11/17/2021 0:00	$21,989.74	$30,357.17
  11/16/2021 0:00	$37,128.55	$72,194.82
  11/15/2021 0:00	$6,564.91
  11/13/2021 0:00		$25,637.27
  11/12/2021 0:00	$17,404.07	$11,558.63
  11/11/2021 0:00	$15,845.59	$18,377
  11/10/2021 0:00	$14,042.51	$34,213.14
  11/9/2021 0:00	$11,786.98	$36,534.50
  11/8/2021 0:00	$19,856	$0
  11/6/2021 0:00		$35,570.67
  11/5/2021 0:00	$17,466.67	$19,411.81
  11/4/2021 0:00	$30,881.84	$15,212.39
  11/3/2021 0:00	$13,875.26	$32,911.77
  11/2/2021 0:00	$7,060.54	$5,371.41
  11/1/2021 0:00	$13,559.69

  

[amitchandak · Accepted solution]

@TheSweeper , In the case of the calculation group using tabular editor

CALCULATE(selectedmeasure(), FILTER('Calendar', 'Calendar'[Date] < TODAY()))

Last year

CALCULATE(selectedmeasure(), sameperiodlastyear('Calendar'[Date]), FILTER('Calendar', 'Calendar'[Date] < date(year(TODAY())-1 ,month(Today()) , day(today()))))

or

CALCULATE(selectedmeasure(), dateadd('Calendar'[Date],-1, year), 'Calendar'[Date] < date(year(TODAY())-1 ,month(Today()) , day(today())) )

[Anonymous · Accepted solution]

Hi  @TheSweeper ,

I created some data:

Here are the steps you can follow：

1. Create a calendar table.

Slice = CALENDARAUTO()

2. Create measure.

Collections LY by trend date =
CALCULATE(SUM('Calendar'[Collections Total]),
FILTER(ALL('Calendar'),'Calendar'[Date]=DATE(YEAR(MAX('Calendar'[Date]))-1,MONTH(MAX('Calendar'[Date])),DAY(MAX('Calendar'[Date])))))

Flag =
IF(MAX('Calendar'[Date])>=MIN('Slice'[Date])&&MAX('Calendar'[Date])<=MAX('Slice'[Date]),1,0)

3. Place Measure[Flag] in the filter, select is=1, and apply filter.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

Does this measure work?

Collections LY by trend date = if(min('Calendar'[Date])<=TODAY(),CALCULATE([Collections Total],SAMEPERIODLASTYEAR('Calendar'[Date])),blank())

[amitchandak · Accepted solution]

@TheSweeper , In the case of the calculation group using tabular editor

CALCULATE(selectedmeasure(), FILTER('Calendar', 'Calendar'[Date] < TODAY()))

Last year

CALCULATE(selectedmeasure(), sameperiodlastyear('Calendar'[Date]), FILTER('Calendar', 'Calendar'[Date] < date(year(TODAY())-1 ,month(Today()) , day(today()))))

or

CALCULATE(selectedmeasure(), dateadd('Calendar'[Date],-1, year), 'Calendar'[Date] < date(year(TODAY())-1 ,month(Today()) , day(today())) )