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´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:
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_Date | Quantity |
2019/01/01 | 10 |
2019/01/02 | -1 |
2019/01/02 | -6 |
2019/01/03 | -2 |
2019/01/03 | 15 |
2019/01/04 | -3 |
2019/01/04 | -5 |
2019/01/04 | -1 |
2019/01/05 | 5 |
2019/01/05 | -2 |
Calendar:
Calendar = CALENDAR(DATE(2019;01;01); DATE(2019;01;05))
The goal would be to obtain something like this:
Solved! Go to Solution.
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?
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 Yes! I overlooked the 'ALL'. Thanks!
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(
Then using this column as values in the visualization.
@Anonymous If it didn´t need to be dynamic to slicers that would be another option, thanks!
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |