Problem with current month and LM same period error when no. of days is a mismatch

[monojchakrab]

Hi All,

While working on a table, which should return the MTD sales for the current month and the same period sales for M-1, I am getting an erroneous result, when, e.g. the current month has 31 days and the last month has 30 days (like Jul Vs. Jun).

Even for Jun - it is somehow computing a sales value for 31st of the month, when Jun cannot have any sales on 31/06, since the last date of the month is 30/06.

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 think if I can get to workaround 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.

Any help appreciated

regds.,

[lbendlin · Accepted solution]

That's how it is.  It is a fact that months have different number of days. It is also a fact that the weekday pattern differs between months.  So even if you compare June 1 - June 10 to July 1 - July 10  you are still comparing apples to oranges because June and July start on different weekdays.

You can experiment with DATEADD and EDATE to see how Power BI "solves" that problem . You have already found most of the rules. 

There is NOTHING you can do against these facts.  Accept them. Accept the imperfections.