Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mteresi
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
amitchandak
Super User
Super User

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

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

 

Remove ENDOFMONTH and try

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.