Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 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:
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:
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!
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:
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:
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |