Total month to date for last year until a specific day of the month

Question

I want to calculate the sum of MTD sales per day for the same period of last year, only until a specific day - the day of the last sales transaction.

So for example, if the filter is year= 2019 and month= 2, I need the MTD sales for february 2018 until the 5th, calculated day by day:

MTDSales=
VAR MyDay = 5
RETURN
CALCULATE(
  TOTALMTD(SUM(Sales); Calendar[Date]);
  SAMEPERIODLASTYEAR(Calendar[Date]);
  //here I need another filter to stop on the 5th!
)

Edit:

Please have a look at this link to see the sample data.

The measures I'm trying to build are: Sales MTD CY
Sales MTD LY Sales MTD CY*
Sales MTD LY* Sales MTD CY**
Sales MTD LY**

Thanks for helping!

Alexis Olson Alexis Olson · Accepted Answer · 2019-02-05T20:35:18

I'm assuming that you are using 5 since today is February 5th.

You can get MTDSales for the current month like this:

MTDSales = 
VAR DateRange = DATESBETWEEN( Calendar[Date], EOMONTH(TODAY(), - 1) + 1, TODAY() )
RETURN CALCULATE( SUM( Sales ), DateRange )

To match that up with the previous year, just use SAMEPERIODLASTYEAR.

LastYearMTDSales = 
VAR DateRange = DATESBETWEEN( Calendar[Date], EOMONTH(TODAY(), - 1) + 1, TODAY() )
RETURN CALCULATE( SUM( Sales ), SAMEPERIODLASTYEAR(DateRange) )

If you want to use a different date than TODAY, just specify that date as a variable and pass it into the DateRange variable where TODAY appears.

If you want to find the MTDSales up to the 5th day of the month (assuming you have the month in your filter context), try this

MTDSales = 
Var MyDay = 5
VAR MyDate = MIN( Calendar[Date] ) + MyDay - 1
VAR DateRange = DATESBETWEEN( Calendar[Date], EOMONTH(MyDate, -1) + 1, MyDate )
RETURN CALCULATE( [Sum of Sales], DateRange )

Then for the previous year, you can reuse that measure, but shifted:

PrevYearMTDSales = 
CALCULATE( [MTDSales], ALL( Calendar ), SAMEPERIODLASTYEAR( Calendar[Date] ) )

Edit: After looking at your PBIX, I realized that I had made the wrong assumption about your filter context. Since you are looking to write a measure at the date level, try this instead:

Sales MTD CY =
VAR MyDay = 5
VAR CurrentDate = MAX ( 'Calendar'[Date] )
VAR MyDate = EOMONTH ( CurrentDate, -1 ) + MIN ( MyDay, DAY ( CurrentDate ) )
RETURN
    CALCULATE (
        TOTALMTD ( SUM ( Sales[Sales] ), 'Calendar'[Date] ),
        FILTER ( 'Calendar', 'Calendar'[Date] <= MyDate )
    )

The previous year measure can still be done referencing this measure and shifting.

Total month to date for last year until a specific day of the month

2 Answers