Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
So we want to compare current MTD to last month but for the same amount of days, so it would compare current MTD(4/1/2021-4/28/2021) to the same time frame of last month (3/1/2021-3/28/2021).
Here I wrote dax like this,
Solved! Go to Solution.
HI @SwathiKundar,
You can try to use the date function to manually define the filter range for calculating.
Revenue LM =
VAR _list =
VALUES ( Dates[Date] )
VAR _min =
MINX ( _list, [Date] )
VAR _max =
MAXX ( _list, [Date] )
RETURN
CALCULATE (
SUM ( table[Revenue] ),
FILTER (
ALLSELECTED ( table ),
[Date]
>= DATE ( YEAR ( _min ), MONTH ( _min ) - 1, DAY ( _min ) )
&& [Date]
<= DATE ( YEAR ( _max ), MONTH ( _max ) - 1, DAY ( _max ) )
)
)
Regards,
Xiaoxin Sheng
OK... What happens when the current month has 31 days and the previous one has 30? If you happen to be on the 31st and calculate MTD, you won't get 31 days from the month before. What do you want to do in such a situation? Also, in Feb you've got only 28 days, so what happens when you're on 29th, 30th, 31st of March?
// Please note that for this to work
// the Dates table must be a true date
// table marked as such in the model.
[Revenue MTD LM (Exact)] =
// Dates[DayNumberOfMonth] goes from 1 through to
// the last day of the month, from 1 thru to 28/29/30/31.
var LastVisibleDayNumberOfMonth = MAX( Dates[DayNumberOfMonth] )
var LastVisibleMonthID = MAX( Dates[MonthID] )
var PreviousMonthDaysOfInterest =
CALCULATETABLE(
DISTINCT( Dates[Date] ),
// Dates must also have a column that stores
// a sequence numbering each month in the table.
// Starting from 1 or 0 and going up without gaps.
// This sequence is a unique identifier for all the
// months across all years, so it's NOT a number
// from 1 to 12. It goes up by 1 each time a new
// month is encountered. Call such a column MonthID.
Dates[MonthID] = LastVisibleMonthID - 1,
Dates[DayNumberOfMonth] <= LastVisibleDayNumberOfMonth,
ALL( Dates )
)
var PreviousMonthHasEnoughDays =
COUNTROWS( PreviousMonthDaysOfInterest )
= LastVisibleDayNumberOfMonth
var Result =
IF( PreviousMonthHasEnoughDays,
// So, this measure will show up only when
// there's enough days in the month before.
CALCULATE(
[Revenue], // this should be the base measure
PreviousMonthDaysOfInterest
)
)
RETURN
Result
HI @SwathiKundar,
You can try to use the date function to manually define the filter range for calculating.
Revenue LM =
VAR _list =
VALUES ( Dates[Date] )
VAR _min =
MINX ( _list, [Date] )
VAR _max =
MAXX ( _list, [Date] )
RETURN
CALCULATE (
SUM ( table[Revenue] ),
FILTER (
ALLSELECTED ( table ),
[Date]
>= DATE ( YEAR ( _min ), MONTH ( _min ) - 1, DAY ( _min ) )
&& [Date]
<= DATE ( YEAR ( _max ), MONTH ( _max ) - 1, DAY ( _max ) )
)
)
Regards,
Xiaoxin Sheng
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 |
---|---|
15 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
29 | |
3 | |
3 | |
2 | |
2 |