Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Supipa
Frequent Visitor

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: 

Supipa_0-1636111434010.png

Bets Last Month = CALCULATE ([Bets], PARALLELPERIOD('Calendar'[Date], -1, MONTH))
Bets = sum([Bet])
 
thank you in advance.
 
1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

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

View solution in original post

2 REPLIES 2
PaulOlding
Solution Sage
Solution Sage

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

Hi @PaulOlding ,

 

Thank you sooo much. This works perfectly!! 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.