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!
