MTD Vs MTD-1 sales : blanking out data when dates are not same

[monojchakrab]

Hi All,

I am working on a table as below :

Date	MTD Val	MTD M-1 Val
31-07-2022 00:00	195373	186524
30-07-2022 00:00	195213	177652
29-07-2022 00:00	191573	175702
28-07-2022 00:00	186692	172152
27-07-2022 00:00	179318	167008
26-07-2022 00:00	172381	161800
25-07-2022 00:00	167000	156372
24-07-2022 00:00	161025	149775
23-07-2022 00:00	147072	141296
22-07-2022 00:00	138406	133241
21-07-2022 00:00	134106	127533
20-07-2022 00:00	129963	122198
19-07-2022 00:00	122576	116012
18-07-2022 00:00	114673	110546
17-07-2022 00:00	109045	102813
16-07-2022 00:00	102849	96372
15-07-2022 00:00	95879	90932
14-07-2022 00:00	91418	87406
13-07-2022 00:00	84691	78923
12-07-2022 00:00	79694	69576
11-07-2022 00:00	75122	64915
10-07-2022 00:00	67852	58055
09-07-2022 00:00	62307	50169
08-07-2022 00:00	56258	45220
07-07-2022 00:00	48935	39483
06-07-2022 00:00	42967	33559
05-07-2022 00:00	37965	29479
04-07-2022 00:00	32318	25682
03-07-2022 00:00	25708	19068
02-07-2022 00:00	16504	14614
01-07-2022 00:00	8873	8335

The code for MTD is as follows :

MTD Val = 
CALCULATE(
    'Measures tray'[Total Sales],DATESMTD('Date Table'[Date])
    )

& the code for MTD-1 is as follows :

MTD M-1 Val = 
Var _Maxdate = MAX('Date Table'[Date])
Var _Startdate = EOMONTH(_Maxdate,-2)+1
Var _Enddate = _Startdate+DAY(_Maxdate)-1
return
CALCULATE(
   'Measures tray'[Total Sales],
DATESBETWEEN('Date Table'[Date],_Startdate,_Enddate)
)

While the code works till the 30th of every month and there is an exact same date for the previous month, the MTD-1 returns wrong figures when the MTD-1 is a 30 day month or a 28/29 day month like in Feb.

Is there a way to modify the MTD-1 code to blank out the sales figure, in case the day(max(month[date]-1) <>day(max(month[date])

Many thanks in anticipation

best regds.,

[monojchakrab]

hi @amitchandak , Thanks for the quick response.

I was trying a different approach for MTD-1 to solve for different days of the month at one shot as below :

DEFINE 

VAR _Maxdate = MAX('Date Table'[Date])
VAR _Startdate = EOMONTH(_Maxdate,-2)+1
VAR _Countdays =
                SWITCH(TRUE(),
                MONTH(_Startdate) IN {4,6,9},29,
                MONTH(_Startdate)=2,27,30)
--VAR _Countdays = DAY(_Maxdate)-DAY(_Startdate)
VAR _Enddate = _Startdate+_Countdays
EVALUATE
/*{
CALCULATE(
   'Measures tray'[Total Sales],*/

DATESBETWEEN('Date Table'[Date],_Startdate,_Enddate)

The code is working fine and returning the dates between 01/06 and 30/06, thus correctly computing the days to add to the starting day, which as per the code, will always be the 1st of the month.

The problem is, when I run it with the calculate function - it returns the same value for MTD-1 for the entire date range as below :

But when I am using this code :

_Countdays = DAY(_Maxdate)-DAY(_Startdate)

to compute the no. of days to add to the start date, it is returning the MTD-1 sales figure correctly for all dates in the range, except, 31-07 , since there is no existing MTD-1 date for 31/06, which is a 30 day month.

I can think if I can get to workwround with the code in the 1st block as above, then I will have solved the problem for all the months with 30 days and 28 days, like Feb, Apr, Jun & Sep.

Would you be able to wrap your head around this?

Many thanks in anticipation

[ChenwuZhu_Gmail · Accepted solution]

Hi @monojchakrab ,

Maybe you can try this formula.

CALCULATE (
    'Measures tray'[Total Sales],
    FILTER (
        PREVIOUSMONTH ( 'Date Table'[Date] ),
        DAY ( [Date] ) <= DAY ( MAX ( 'Table Date'[Date] ) )
    )
)

Result:

Best regards.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[monojchakrab]

That worked @ChenwuZhu_Gmail and I am using this as an alternative measure. The one suggested by @amitchandak also works actually.

Thank yo both

[amitchandak]

@monojchakrab , Try like

LMTD Val =
CALCULATE(
'Measures tray'[Total Sales],DATESMTD(dateadd('Date Table'[Date],-1, month))
)

or

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))