cancel
Showing results for
Did you mean:
Frequent Visitor

## compare current MTD to last month but for the same amount of day

Hello All,

So we want to compare current MTD to last month but for the same amount of days, so it would compare current MTD(4/1/2021-4/28/2021) to the same time frame of last month (3/1/2021-3/28/2021).

Here I wrote dax like this,

Revenue LM = CALCULATE(SUM(table[Revenue]), PREVIOUSMONTH(Dates[Date])) but this will returns previous month complete data but I need to display till (3/1/2021-3/28/2021). Can anyone help with the solution?

2 REPLIES 2
Solution Sage

OK... What happens when the current month has 31 days and the previous one has 30? If you happen to be on the 31st and calculate MTD, you won't get 31 days from the month before. What do you want to do in such a situation? Also, in Feb you've got only 28 days, so what happens when you're on 29th, 30th, 31st of March?

``````// Please note that for this to work
// the Dates table must be a true date
// table marked as such in the model.

[Revenue MTD LM (Exact)] =
// Dates[DayNumberOfMonth] goes from 1 through to
// the last day of the month, from 1 thru to 28/29/30/31.
var LastVisibleDayNumberOfMonth = MAX( Dates[DayNumberOfMonth] )
var LastVisibleMonthID = MAX( Dates[MonthID] )
var PreviousMonthDaysOfInterest =
CALCULATETABLE(
DISTINCT( Dates[Date] ),
// Dates must also have a column that stores
// a sequence numbering each month in the table.
// Starting from 1 or 0 and going up without gaps.
// This sequence is a unique identifier for all the
// months across all years, so it's NOT a number
// from 1 to 12. It goes up by 1 each time a new
// month is encountered. Call such a column MonthID.
Dates[MonthID] = LastVisibleMonthID - 1,
Dates[DayNumberOfMonth] <= LastVisibleDayNumberOfMonth,
ALL( Dates )
)
var PreviousMonthHasEnoughDays =
COUNTROWS( PreviousMonthDaysOfInterest )
= LastVisibleDayNumberOfMonth
var Result =
IF( PreviousMonthHasEnoughDays,
// So, this measure will show up only when
// there's enough days in the month before.
CALCULATE(
[Revenue], // this should be the base measure
PreviousMonthDaysOfInterest
)
)
RETURN
Result``````

Community Support

HI @SwathiKundar,

You can try to use the date function to manually define the filter range for calculating.

``````Revenue LM =
VAR _list =
VALUES ( Dates[Date] )
VAR _min =
MINX ( _list, [Date] )
VAR _max =
MAXX ( _list, [Date] )
RETURN
CALCULATE (
SUM ( table[Revenue] ),
FILTER (
ALLSELECTED ( table ),
[Date]
>= DATE ( YEAR ( _min ), MONTH ( _min ) - 1, DAY ( _min ) )
&& [Date]
<= DATE ( YEAR ( _max ), MONTH ( _max ) - 1, DAY ( _max ) )
)
)``````

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks