Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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())
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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
44 | |
28 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |