PYTD Dax for 2 years prior

[Thackeb]

Looking for guidance. I'm working with read-only tables, therefore I cannot add calculated columns or new tables. I'm also omitting any type of date slicer, as when using one, the 2019 totals were not accurate. For security reasons, I cannot load the PBIX...sorry!

I'm comparing 2021 vs 2019 in this example.  I'm using this DAX for my 2019 YTD Net Sales:  (I'm not sure why how the calculation works, looking at 2019 Net Sale $, however it is and that is not the problem. )

2019 YTD Net Sal $ =

CALCULATE('TransDet'[2019 Net Sale $],

DATEADD(DATESYTD('TransDet'[TRANS_DT]),-2,YEAR))

It is omitting November and December, but is not recognizing October as a partial month. This calculation feeds the YOY % and YOY $ fields. 

Month	2019 Net Sale $	*2021 Net Sale $	2019 YOY % Net Sale	2019 YOY $ Net Sale	2019 YTD Net Sal $
January	$12,272,905	$11,147,676	-9.20%	($1,125,229)	$12,272,905
February	$9,284,644	$8,736,575	-5.90%	($548,069)	$9,284,644
March	$10,837,664	$11,684,764	7.80%	$847,099	$10,837,664
April	$10,874,286	$10,484,593	-3.60%	($389,693)	$10,874,286
May	$10,545,673	$10,056,726	-4.60%	($488,946)	$10,545,673
June	$10,077,091	$11,411,752	13.20%	$1,334,661	$10,077,091
July	$11,151,063	$11,069,816	-0.70%	($81,247)	$11,151,063
August	$11,068,113	$13,212,906	19.40%	$2,144,793	$11,068,113
September	$10,359,549	$12,965,535	25.20%	$2,605,986	$10,359,549
October	$10,857,287	$4,265,289	-60.70%	($6,591,999)	$10,857,287
November	$9,468,558
December	$9,577,631

On a separate table, I'm comparing 2021 vs 2020 using this Dax: 

*2020 YTD Net Sal $ = CALCULATE

(CALCULATE([*2020 Net Sale $],

SAMEPERIODLASTYEAR('Date'[DT])),

FILTER('Date',[DT] < TODAY()))

This is working correctly, but despite efforts, I couldn't get it to work for the 2019 YTD. 

Month	2020 Net Sale $	*2021 Net Sale $	*YOY $ Net Sal	*YOY % Net Sal	*2020 YTD Net Sal $
January	$11,552,133	$11,147,676	($404,457)	-3.50%	$11,552,133
February	$9,703,347	$8,736,575	($966,772)	-10.00%	$9,703,347
March	$18,615,851	$11,684,764	($6,931,088)	-37.20%	$18,615,851
April	$11,685,117	$10,484,593	($1,200,524)	-10.30%	$11,685,117
May	$11,115,515	$10,056,726	($1,058,789)	-9.50%	$11,115,515
June	$12,891,233	$11,411,752	($1,479,481)	-11.50%	$12,891,233
July	$13,159,227	$11,069,816	($2,089,410)	-15.90%	$13,159,227
August	$12,982,771	$13,212,906	$230,135	1.80%	$12,982,771
September	$11,329,095	$12,965,535	$1,636,440	14.40%	$11,329,095
October	$11,813,389	$4,265,289	$542,248	14.60%	$3,723,041
November	$14,885,798
December	$11,911,255

I've worked on this for 2 days, have googled everything possible and still cannot solve it. Any help will be tremendously appreciated! 🙂 

[Thackeb]

The first option worked!!! Thank you so much!!!!

[v-stephen-msft · Accepted solution]

Hi @Thackeb ,

Do you want to calculate the sum in different years, from January to the current date? For example, today is October 15, 2021, if 2019 is the sum of January 1, 2019 to October 15, 2019.

Try this measure

Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]>=DATE(2019,1,1)&&[Date]<=DATE(2019,MONTH(TODAY()),DAY(TODAY()))))

 

If you want dynamic results, you can filter the years, create the following table by entering data, and put it in the slicer.

Create the measure

Measure 2 = var _year=SELECTEDVALUE('Table (2)'[Year])
return CALCULATE(SUM('Table'[Value]),FILTER('Table',[Date]>=DATE(_year,1,1)&&[Date]<=DATE(_year,MONTH(TODAY()),DAY(TODAY()))))

Best Regards,

Stephen Tao

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