cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thackeb
Helper I
Helper I

PYTD Dax for 2 years prior

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. 

Month2019 Net Sale $*2021 Net Sale $2019 YOY % Net Sale2019 YOY $ Net Sale2019 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,7647.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,75213.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,90619.40%$2,144,793$11,068,113
September$10,359,549$12,965,53525.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. 
Month2020 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,1351.80%$12,982,771
September$11,329,095$12,965,535$1,636,44014.40%$11,329,095
October$11,813,389$4,265,289$542,24814.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! 🙂 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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

1.png 

 

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

vstephenmsft_0-1634265355636.png

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

2.png

 

 

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.

 

View solution in original post

2 REPLIES 2
Thackeb
Helper I
Helper I

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

v-stephen-msft
Community Support
Community Support

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

1.png 

 

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

vstephenmsft_0-1634265355636.png

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

2.png

 

 

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.

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors