cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SwathiKundar
Frequent Visitor

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

Hello All,
 
SwathiKundar_1-1619636262198.png

 


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?

Thanks in advance.
2 REPLIES 2
daxer-almighty
Solution Sage
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

 

v-shex-msft
Community Support
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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors