cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mteresi Frequent Visitor
Frequent Visitor

Unable get correct Value for Last Year Month To Date Sales with other solutions.

 

Here is the data (summary as I didn’t want to include every date for the last year:

 

Total Sales Now

Date

Last Years Sales

 

1/31/2020 0:00

$12,367.96

 

1/30/2020 0:00

 
 

1/29/2020 0:00

$15,723.28

 

1/28/2020 0:00

$18,038.89

 

1/27/2020 0:00

$3,362.73

 

1/26/2020 0:00

$10,917.61

 

1/25/2020 0:00

$13,247.13

 

1/24/2020 0:00

$15,811.38

 

1/23/2020 0:00

$14,465.23

 

1/22/2020 0:00

$18,136.55

 

1/21/2020 0:00

$16,142.63

 

1/20/2020 0:00

$3,614.38

 

1/19/2020 0:00

$10,473.57

 

1/18/2020 0:00

$16,908.48

$833.23

1/17/2020 0:00

$13,947.81

$16,154.53

1/16/2020 0:00

$13,232.94

$10,602.68

1/15/2020 0:00

$18,420.96

$20,217.28

1/14/2020 0:00

$23,380.57

$17,253.92

1/13/2020 0:00

$2,709.92

$3,813.49

1/12/2020 0:00

$14,216.69

$11,255.44

1/11/2020 0:00

$19,554.87

$22,007.32

1/10/2020 0:00

$16,359.14

$16,733.07

1/9/2020 0:00

$16,926.76

$13,852.82

1/8/2020 0:00

$18,873.53

$19,410.26

1/7/2020 0:00

$17,862.72

$23,792.40

1/6/2020 0:00

$3,261.55

$8,250.68

1/5/2020 0:00

$13,854.18

$13,157.16

1/4/2020 0:00

$23,239.60

$21,697.46

1/3/2020 0:00

$16,629.73

$18,735.57

1/2/2020 0:00

$12,591.46

 

1/1/2020 0:00

 
   

$12,367.96

1/31/2019 0:00

$13,956.93

 

1/30/2019 0:00

$13,997.49

$15,723.28

1/29/2019 0:00

$14,874.93

$18,038.89

1/28/2019 0:00

 

$3,362.73

1/27/2019 0:00

$11,983.21

$10,917.61

1/26/2019 0:00

$13,821.75

$13,247.13

1/25/2019 0:00

$14,061.95

$15,811.38

1/24/2019 0:00

$10,601.43

$14,465.23

1/23/2019 0:00

$15,949.98

$18,136.55

1/22/2019 0:00

$13,112.73

$16,142.63

1/21/2019 0:00

 

$3,614.38

1/20/2019 0:00

$14,289.89

$10,473.57

1/19/2019 0:00

$10,160.42

$16,908.48

1/18/2019 0:00

$12,962.34

$13,947.81

1/17/2019 0:00

$8,433.85

$13,232.94

1/16/2019 0:00

$14,873.91

$18,420.96

1/15/2019 0:00

$17,209.32

$23,380.57

1/14/2019 0:00

 

$2,709.92

1/13/2019 0:00

$10,088.86

$14,216.69

1/12/2019 0:00

$13,661.52

$19,554.87

1/11/2019 0:00

$13,606.55

$16,359.14

1/10/2019 0:00

$12,268.35

$16,926.76

1/9/2019 0:00

$16,776.23

$18,873.53

1/8/2019 0:00

$15,413.43

$17,862.72

1/7/2019 0:00

 

$3,261.55

1/6/2019 0:00

$11,962.24

$13,854.18

1/5/2019 0:00

$13,914.46

$23,239.60

1/4/2019 0:00

$13,441.09

$16,629.73

1/3/2019 0:00

$10,737.29

$12,591.46

1/2/2019 0:00

$11,833.09

 

1/1/2019 0:00

 

 

Currently working MTD Measure:

 

MTD = CALCULATE(Sum(ItemSales[Total Sales]),DATESMTD('Date'[Date]))

 

  • I think I need to use this because the original table I am working with has multiple sales with the same date.

 

I used this measure to get the daily sales from last year:

 

Last Years Sales = CALCULATE([Total Sales Now], SAMEPERIODLASTYEAR('Date'[Date]))

 

Where the measure [Total Sales Now] = Sum( ItemSales[Total Sales])

 

Now attempting to get MTD from Last Year if I use the common solution found online

MTD LY = CALCULATE(Sum(ItemSales[Total Sales]), SAMEPERIODLASTYEAR(DATESMTD(ItemSales[Date]))) Returns This Where the top number is actually the sum of the entire month and the tile reflects that:

 

Picture1.png

 

This Measure:

 

MTD LY = CALCULATE([MTD], SAMEPERIODLASTYEAR(DATESMTD('Date'[Date])))

 Returns this which is also not right but has the value I need on 1/17/2020 of $245,062.43:

 

 

Picture2.png

 

Any help would be greatly appreciated or if you would like me to try something different. I have attempted to use around 10 "solutions" for this problem from this community so im not sure how my situation is different at this time. 

 

Thank you in advance for your help!

 

4 REPLIES 4
Super User IV
Super User IV

Re: Unable get correct Value for Last Year Month To Date Sales with other solutions.

for Last year MTD

last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

 

But MTD means it will show the cumulative total for that month

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

Hope these helps





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


mteresi Frequent Visitor
Frequent Visitor

Re: Unable get correct Value for Last Year Month To Date Sales with other solutions.

Thank you. I tried that below. To confirm, in the initial table, those values for total sales now are a measure created by summing all of the sales for a particular date, which there are many individual entries per date:

 

MTD LAST YEAR =
CALCULATE(SUM(ItemSales[Total Sales]),DATESMTD(ENDOFMONTH(dateadd(ItemSales[Date],-12,MONTH))))
 
Capture1.PNG
 

 

Capture2.PNG

 

Super User IV
Super User IV

Re: Unable get correct Value for Last Year Month To Date Sales with other solutions.

Remove ENDOFMONTH and try

CALCULATE(SUM(ItemSales[Total Sales]),DATESMTD(dateadd(ItemSales[Date],-12,MONTH)))





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


mteresi Frequent Visitor
Frequent Visitor

Re: Unable get correct Value for Last Year Month To Date Sales with other solutions.

Thanks. I think closer with that change as the top number for today's date is correct but the tile still shows the total for the month as does the bottom BOLDED number at the bottom on the table view. See below the table and the tile:

 

Capture3.PNGCapture4.PNG

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors