Forum Discussion

Supipa's avatar
Supipa
Frequent Visitor
4 years ago
Solved

How to do MTD

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's avatar
    PaulOlding
    4 years ago

    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())

2 Replies

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    4 years ago

    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())