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

Filter axis but not data

I´m going to write an example of the question I´m trying to solve:

A Transaction table records each time a Coca Cola is withdrawed from or added to a shelve. It is connected with a Date table.

This measure calculates the stock of the shelve at every point in time:

Stock = 
CALCULATE(
	SUM('Transaction'[Quantity]);
	FILTER(
		ALLSELECTED(Calendar[Date]);
		ISONORAFTER(Calendar[Date]; MAX(Calendar[Date]); DESC)
	)
)

Which, using a continous date axis, produces a visual similar to the following:

Stock by Date.png

I´d like to add a slicer to the report that filters the dates of the X axis but does not filter the data itself. For example, filtering for the year 2016 in the axis would still use the transactions from 2015, 2014, etc., so the stock of the shleve can be calculated. Is it posible?

++++++++++++++++++++++++++++++++++++++++++++++++++++

Here are some tables to tinker with:

Transactions:

T_DateQuantity
2019/01/0110
2019/01/02-1
2019/01/02-6
2019/01/03-2
2019/01/0315
2019/01/04-3
2019/01/04-5
2019/01/04-1
2019/01/055
2019/01/05-2

 

Calendar:

Calendar = CALENDAR(DATE(2019;01;01); DATE(2019;01;05))

 

Example1.png

The goal would be to obtain something like this:

Example3.png

Example2.png

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi, 

 

If you use ALL instead of ALLSELECTED: 

Stock = 
CALCULATE(
	SUM(('Transactions'[Quantity]));
	FILTER(
		ALL(Calendar[Date]);
		ISONORAFTER(Calendar[Date]; MAX(Calendar[Date]); DESC)
	)
)

 is this what you want to achieve?

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi, 

 

If you use ALL instead of ALLSELECTED: 

Stock = 
CALCULATE(
	SUM(('Transactions'[Quantity]));
	FILTER(
		ALL(Calendar[Date]);
		ISONORAFTER(Calendar[Date]; MAX(Calendar[Date]); DESC)
	)
)

 is this what you want to achieve?

Anonymous
Not applicable

@Anonymous Yes! I overlooked the 'ALL'. Thanks!

Anonymous
Not applicable

Hi @Anonymous 

 

My approach would be to create a new column in the "Calendar" table to calculate the stock on the shelve at that moment. See dax for column below:

QtyOnShelf = CALCULATE(

                        SUM(Transactions[Quantity]) ,
                                 FILTER(Transactions , Transactions[T_Date] <= Calender[Date])
                         )

 

Then using this column as values in the visualization.

 
 
 
Anonymous
Not applicable

@Anonymous If it didn´t need to be dynamic to slicers that would be another option, thanks!

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.