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

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:

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

thank you in advance.

1 ACCEPTED SOLUTION
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())``
2 REPLIES 2
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())``
Frequent Visitor

Hi @PaulOlding ,

Thank you sooo much. This works perfectly!!

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors