How to do MTD

[Supipa]

Hi,

I've been trying to do a MoM comparison and all is good if it is up to last month, when I get to the current month i would like to compare the previous month up to the current day. For example, if i check today (5th of November) the sum of bets, I want to have a column with the current month sum of bets and on the previous month column i would like to see the sum of bets up to the same day of the previous month (5th of October) and not the sum of the whole month of October. Any tips?

My current output and formulas: 

Bets Last Month = CALCULATE ([Bets], PARALLELPERIOD('Calendar'[Date], -1, MONTH))

Bets = sum([Bet])

thank you in advance.

[PaulOlding · Accepted solution]

Hi @Supipa 

Here's a measure for you to try:

Bets Last Month = 
VAR _Today = TODAY()
VAR _LastDayThisMonth = EOMONTH(_Today, 0)
VAR _FilteredMonth = MAX('Calendar'[Date])
VAR _Result = 
IF(
    _LastDayThisMonth = _FilteredMonth,
    // get last month up to today's day of month
    CALCULATE(
        [Bets], 
        DATEADD(
            FILTER(VALUES('Calendar'[Date]), DAY('Calendar'[Date]) <= DAY(_Today)), 
            -1, 
            MONTH
        )
    ),
    // get all of last month
    CALCULATE ([Bets], PARALLELPERIOD('Calendar'[Date], -1, MONTH))
)
RETURN
    _Result

As you can see it's based on today's date.  I put it as a variable in case you wanted to use the latest date in the dataset instead.  In that case you'd put this instead

VAR _Today = CALCULATE(MAX(Bets[Date]), REMOVEFILTERS())

[Supipa]

Hi @PaulOlding ,

Thank you sooo much. This works perfectly!!