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 am attempting to create a measure to show revenue over the past 30 days and have it ignore any date slicers on the page.
I have this slicer that works to do this, but it only shows totals instead of breaking it down by day:
M_BookedRevenueMonth =
CALCULATE(
SUM(RevenueChannel[BookedRevenue]),
FILTER(
ALL('Date'),
'Date'[Date] >= TODAY()-30
&& 'Date'[Date] < TODAY()
)
)
I have then created a new measure that shows me the break down by date, but I cannot figure out how to ignore the slicers within a groupby():
M_TestMeasure =
CALCULATE (
SUMX (
FILTER (
GROUPBY (
RevenueChannel,
RevenueChannel[SearchType],
RevenueChannel[ChannelType],
'Date'[Date],
'Date'[AccountingWeek],
'Date'[WeekNumber],
'Date'[AccountingMonthEnglishAbbrYear],
'Date'[AccountingMonth],
'Date'[AccountingYear],
"Revenue", SUMX ( CURRENTGROUP (), RevenueChannel[BookedRevenue] )
),
'Date'[Date] >= TODAY()-30
&& 'Date'[Date] < TODAY()
),
[Revenue]
)
)
Any ideas?
@zfemmer , If want to ignore the filter, best is disable interaction.
Most of the time when you give you own filter you will end up grouping data into one date.
For that you refer to this video, there is a solution for that: https://www.youtube.com/watch?v=duMSovyosXE
The issue with this is that I have multiple measures feeding into the same visual.
So the end user selects a measure they want; WTD, MTD, YTD, or a specific date range.
I want the date range slicer to feed into the date range measure, but not the others.
Does this make sense?
Hi @zfemmer
Have you tried ALL( RevenueChannel) as the first argument for the GROUPBY ( )?
Otherwise add ALL( RevenueChannel) as last argument to the outermost CALCULATE( )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Can you tell me where to put it?
I thought this was the answer, but I could not for whatever reason figure out where the ALL would go within the group by.
In the first argument, base table, of GROUPBY ( 😞
M_TestMeasure =
CALCULATE (
SUMX (
FILTER (
GROUPBY (
ALL ( RevenueChannel ),
RevenueChannel[SearchType],
RevenueChannel[ChannelType],
'Date'[Date],
'Date'[AccountingWeek],
'Date'[WeekNumber],
'Date'[AccountingMonthEnglishAbbrYear],
'Date'[AccountingMonth],
'Date'[AccountingYear],
"Revenue", SUMX ( CURRENTGROUP (), RevenueChannel[BookedRevenue] )
),
'Date'[Date] >= TODAY()-30
&& 'Date'[Date] < TODAY()
),
[Revenue]
)
)
or as second argument for the CALCULATE( ) (which by the way I am not sure why you were using it):
M_TestMeasure =
CALCULATE (
SUMX (
FILTER (
GROUPBY (
RevenueChannel,
RevenueChannel[SearchType],
RevenueChannel[ChannelType],
'Date'[Date],
'Date'[AccountingWeek],
'Date'[WeekNumber],
'Date'[AccountingMonthEnglishAbbrYear],
'Date'[AccountingMonth],
'Date'[AccountingYear],
"Revenue", SUMX ( CURRENTGROUP (), RevenueChannel[BookedRevenue] )
),
'Date'[Date]
>= TODAY () - 30
&& 'Date'[Date] < TODAY ()
),
[Revenue]
),
ALL ( RevenueChannel )
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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 |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |