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.
I need the user to be able to select a date period for a sales table: Year-To-Date (YTD), Month-To-Date (MTD) & Week-To-Date (WTD) - But as MTD includes WTD and YTD includes MTD this can't simply filter on a column (see example below assuming we're currently in Week 3 of July):
Month | Week | Period |
July | Week 1 | MTD |
July | Week 2 | MTD |
July | Week 3 | WTD |
as MTD (weeks 1 & 2) would exclude WTD (week 3) and as you can see MTD needs to include week 3
To overcome this I have:
This seems to be working OK, but only when the SalesTable[Period] column is shown in the table visual - which is causing duplication, so where the table is showing sales by sales team for instance, adding the period column is duplicating sales teams for "MTD" & "MTD/WTD"
Why is it only working when the Period column is shown in the table visual?
Any help would be appreciated as this is driving me crazy and I can't think of another way to achieve this... I don't want users to have to select both MTD & WTD to get MTD sales values
Thanks
Jim
Solved! Go to Solution.
Of course! Normally what you would do is make sure that you have a calendar table attached to your fact table.
This will mean that any filter you apply to your calendar will affect your fact table.
So you can try something along the lines of this:
MTD =
VAR Year_ = YEAR(TODAY())
VAR Month_ = MONTH(TODAY())
Return
Calculate( [Sales] , ALL(Calendar) , Calendar[Year] = Year_ , Calendar[Month] = Month_ , Calendar[Date] <= TODAY())
This should always return the current MTD Sales Value.
Br,
J
Hi @jimbob2285,
Did tex628 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements to find it more quickly.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @jimbob2285 ,
I'd advice you to create 3 different measures instead.
[WTD], [MTD] & [YTD]
Then use a switch statement to swap between them depending on the slicer selection:
Switch measure =
SWITCH(
SELECTEDVALUE( SlicerTable[Period] )
"WTD" , [WTD] ,
"MTD" , [MTD] ,
"YTD" , [YTD]
)
Br,
J
Hi, thanks for this, forgive my ignorance, I'm quite new to DAX, but I'm struggling to understand what the three measures would look like - For instance, for MTD I've tried an if statement to produce a 1 or 0, but that's not working
Could you give me an example please
Here's a link to my example file: Example File
Thanks
Jim
Of course! Normally what you would do is make sure that you have a calendar table attached to your fact table.
This will mean that any filter you apply to your calendar will affect your fact table.
So you can try something along the lines of this:
MTD =
VAR Year_ = YEAR(TODAY())
VAR Month_ = MONTH(TODAY())
Return
Calculate( [Sales] , ALL(Calendar) , Calendar[Year] = Year_ , Calendar[Month] = Month_ , Calendar[Date] <= TODAY())
This should always return the current MTD Sales Value.
Br,
J
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |