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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!