Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure to always show last 7 days of max of slicer selection

Hi, 

 

I have a base measure used in other reports that is calculated differently dependent on the filter context, which is attached below.

 

Now I wan't to use this measure in a "daily" report.

In my daily report, I'm using a slicer to select a interval of 2 dates, for example 10 Oct. 2017 to 11 Oct. 2017.

 

Using the base measure as it, it will only show data for those two dates - as expected. But what I want is for the measure to find the max date selected in the slicer, but then show data for the last 7 dates based on that slicer.


So basically I first want to use the information from the slicer, but then later ignore it. 
Usually I would use a variation of Calculate( [Measure] ; DATESINPERIODE() ) , but that won't really work in this case due to the slicer.

 

How would I go about calculating my desired measure?


Base measure:

Antal Klubmedlemmer = 
VAR
AntalClubMedlemmer =	
CALCULATE(	
	CALCULATE(SUM(Klubmedlem[Samlet antal oprettelser]);
		FILTER(
			All(Periode[Dato]);
			Periode[Dato] <= MAX(Periode[Dato]) )
						))
VAR 
AntalKlubmedlemmerEfterBetaltEllerGratisOprettelse = 
CALCULATE(COUNTROWS(Klubmedlem); LASTDATE(Periode[Dato]); 
	FILTER(Klubmedlem;Klubmedlem[Klubmedlem Status] = "Aktiv"))
RETURN
IF( ISFILTERED('Klubmedlem Medlemskabstype'[Medlemskabstype]); 
				AntalKlubmedlemmerEfterBetaltEllerGratisOprettelse; 
				AntalClubMedlemmer
)
5 REPLIES 5
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

Please refer to this example to see whether it works in your scenario.

 

If you still have any question, please provide more information about sample data and desired output so that I can test for you.

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

As far as I understand the solution, it would require me to have two slicers on the page, which is undesirable.

 

 

i.e. one for my exsisting date table that I'm already using and another for my new filter?

Hi @Anonymous,

 

Based on the solution in above link, it only needs one sigle slicer. We directly add [date] column from date table into slicer.

 

Why do you say you may need two slicers? In your scenario, do you want the slicer to filter other visuals (not only to filter last 7 days)?

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yulgu-msft,

 

The report I'm trying to create, currently has 1 date slicer and two time slicers, with the time slicers only affecting one specific measurement. This allows me to set a specific time frame, i.e. October 21, 8 am to October 23, 8 am and only get revenue from that specific timeframe.

 

Additionally I have a waterfall chart that is currently set to not be interactive to any slicer, but have its own custom date filtering, showing the last 7 days.

 

I now want the waterfall chart to continue showing the last 7 days, but those 7 days should be set by the current date slicer, which would typically only select two dates at a time.

 

I was therefore thinking I could retrieve the max date from the slicer, and then get the visuel to show last 7 days from there.
But as far as I've understood things, my waterfall chart needs to have a seperate date table to achive this.

The extra date table when then need to be filtered based on the max date from the current date table/slicer.

Anonymous
Not applicable

I was thinking along the lines of:

 

the code attached below, but can't wrap my head around how I get the visual to respect and ignore the slicer selection at the same time.

 

Klubmedlemmer seneste 7 dage = 
VAR MaxSlicerDate = MAX(Periode[Dato])
VAR MaxSlicerDateMinus7 = MaxSlicerDate - 7

VAR AntalClubMedlemmer =	
CALCULATE(	CALCULATE(SUM(Klubmedlem[Samlet antal oprettelser]);
							FILTER(
								All(Periode[Dato]);
								Periode[Dato] <= MAX(Periode[Dato]) )))
RETURN
CALCULATE( AntalClubMedlemmer ; 
	ALL(Periode[Dato]);
	FILTER(Periode; 
		DATESBETWEEN(Periode[Dato] ; MaxSlicerDateMinus7 ; MaxSlicerDate )
	) )
			

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.