topic Re: Ignore slicer that works on a column from one table, but apply Filter function on a another in DAX Commands and Tips

Ignore slicer that works on a column from one table, but apply Filter function on a another

arielspalter — Tue, 31 Jan 2023 00:00:25 GMT

Hello everyone,

I have a situation, which I am going to greatly simplify below. I have 2 tables:

The 1st, with the main data:

+-------------+-----------+--------+--------+ | Month Index | Month End | City | Counts | +-------------+-----------+--------+--------+ | 1 | 30-Jan | City 1 | 11 | +-------------+-----------+--------+--------+ | 1 | 30-Jan | City 2 | 12 | +-------------+-----------+--------+--------+ | 1 | 30-Jan | City 3 | 13 | +-------------+-----------+--------+--------+ | 2 | 28-Feb | City 1 | 22 | +-------------+-----------+--------+--------+ | 2 | 28-Feb | City 2 | 23 | +-------------+-----------+--------+--------+ | 2 | 28-Feb | City 3 | 24 | +-------------+-----------+--------+--------+ | 3 | 31-Mar | City 1 | 34 | +-------------+-----------+--------+--------+ | 3 | 31-Mar | City 2 | 35 | +-------------+-----------+--------+--------+ | 3 | 31-Mar | City 3 | 36 | +-------------+-----------+--------+--------+ | | | | | +-------------+-----------+--------+--------+

And the 2nd is a date table with relationship set between the two tables based on the shared column Month End:

+-----------+ | Month End | +-----------+ | 30-Jan | +-----------+ | 28-Feb | +-----------+ | 31-Mar | +-----------+

I have 2 slicers:

The date (from date table) and the city (from main table) - so each slicer is coming from a different table.

Now onto the issue - I need to show the value of any selected city for the last 3 months (as separate measures on 3 different cards), from the selected date.

So for example, if the current slicer selection is "city 2" and March 31, one measure should show 35, the second 23 and the third 12.

I was trying to work with:

Previous Month = CALCULATE( SUM(TABLE[City]), FILTER(TABLE,TABLE[Month Index] = MAX(TABLE[Month Index])-1) )

The issue that because Month filter always shows the most recent month only (this code works):

MAX(TABLE[Month Index])

The previous months is retuned as Blank, since it gets filtered out (this code returns Blank):

MAX(TABLE[Month Index])-1

I need to be able to show the value for the selected city for the last 3 months, from the selected date.

I hope the question is clear and thanks in advance for the help!

Re: Ignore slicer that works on a column from one table, but apply Filter function on a another

amitchandak — Tue, 31 Jan 2023 01:51:38 GMT

arielspalter , As you have date , make you use date table and time intelligence for that

example measures

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

Re: Ignore slicer that works on a column from one table, but apply Filter function on a another

arielspalter — Tue, 31 Jan 2023 03:06:58 GMT

Thank you @amitchandak. Those Time Intelligence function are very useful, and got me where I needed!