Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I'm trying to calculate TOTALMTD for a field but our monthly 'dates' dont align perfectly with a natural calendar date, e.g. what we call "Month 2" rather than simply being February actually comprises all dates from 30/01/2020 to 26/02/2020, I have a dates table which links every natural date to what we call each month internally (CustomMonth).
Is there a way to use TOTALMTD but have it work with my field "CustomMonth" rather than the natural date field? Alternatively another way to get cumulative values from the start of the current "custommonth" until today()? But have it unaffected by slicers/filters on the visual in the same way TOTALMTD is unaffected
I tried to use the following cumulative totals formula which does work with my customMonth field, but filtering to say week 3 of the month shows a value just for that week rather than for the customMonth to week 3:
PTD Sales = Calculate(
sum(Table1[Sales]),
FILTER(
ALLSELECTED(Dates),Dates[Date] <= MAX(Dates[Date])
)
)
The above code is obviously affected by my CustomMonth selections on the visual, whereas I need them implicately limited to the current CustomMonth regardless of visual slices if that makes sense.
Many thanks
Solved! Go to Solution.
@Anonymous , Create a column like // Assuming you have Month Start date
Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
Month Day = DATEDIFF('Date'[Month Start Date],'Date'[Date],Day)+1
and measures like
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
MTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [Month Day] <=max([Month Day])))
LMTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [Month Day] <=max([Month Day])))
@Anonymous , Create a column like // Assuming you have Month Start date
Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
Month Day = DATEDIFF('Date'[Month Start Date],'Date'[Date],Day)+1
and measures like
This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
MTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [Month Day] <=max([Month Day])))
LMTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [Month Day] <=max([Month Day])))
Hey Amit,
This worked for me, thanks! I can't quite follow what your code is doing though, in the interest of learning the why, is there a simplistic explanation for what those columns and measures are doing please?
Please try this expression instead
PTD Sales =
VAR vThisCustomMonth =
MIN ( Dates[CustomMonth] )
RETURN
CALCULATE (
SUM ( Table1[Sales] ),
FILTER (
ALLSELECTED ( Dates ),
Dates[Date]
<= MAX ( Dates[Date] )
),
Dates[CustomDate] = vThisCustomMonth
)
Or, if your visual includes your CustomMonth column, you can just use
PTD Sales = Calculate(
sum(Table1[Sales]),
FILTER(
ALLSELECTED(Dates[Date]),Dates[Date] <= MAX(Dates[Date])
)
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat,
What is the CustomDate field you refer to here? my CustomMonth or something else?
thanks
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |