Forum Discussion
Helper IFilter visual by measure not working
Right I now understand what your issue is. You can fix that by creating a TRUE/FALSE column in your Calendar1 table like this:
IsLastQuarter = VAR _today = FILTER(ALL(Calendar1), Calendar1[Date] = TODAY()) VAR prevQ = CALCULATETABLE(PREVIOUSQUARTER(Calendar1[Date]), _today) RETURN IF(Calendar1[Date] IN prevQ, TRUE, FALSE)Then filter your page on the column IsLastQuarter and then your datetable is always filtered to the last quarter.
On your other question; PREVIOUSQUARTER() takes a table as input and TODAY() returns a datetime value and thus is not valid. A filtered table derived from Calendar1 is a valid input. Hope this helps you out!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Helper I
Resident RockstarThe reason why you can't filter on that measure is because you would create an circular reference. The Measure is evaluated based on the filtered context, but then you want tog filter te context based on the measure (which would be re-evaluated etc).
However, in your specific case: you say you have two measures (lastquarterscore and CustomerScore). In the pbix I only see one measure (lastquarterscore). I don't understand what you are trying to achieve by 'filtering on lastquarterscore' as that measure returns a scalar value for the current context. What you might need is a calculated column and evaluate for every row in the table a certain expression and then use that column as a filter.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
- socksinbox
JarroVGIT - CustomerScore is a measure i.e. sum of customerscore but not explicitly defined.
Right now, customerscore shows the sum of all the rows. However, I just wanted to show the sum for only last quarter. Currently, last quarter measure is doing it and it's hardcoded for now.
So I just want to filter customer score where lastquarter value evalutes to 1.
- JarroVGIT
ALright I see what you are trying to achieve, which is this "However, I just wanted to show the sum for only last quarter."
This is achievable by using the following measure:
LastQ = CALCULATE(SUM('Customer Score'[CustomerScore]), PREVIOUSQUARTER(Calendar1[Date]) )Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
- JarroVGIT
Sorry one last point. PREVIOUSQUARTER() takes the first date of the datecolumn you give as input and then give the previous quarter. In your case, you want the previous quarter relative to today. Here is a measure that filters your Date table to today's date and uses that as input for the PREVIOUSQUARTER() input:
LastQ = VAR _today = FILTER(VALUES(Calendar1[Date]), Calendar1[Date] = TODAY()) RETURN CALCULATE(SUM('Customer Score'[CustomerScore]), PREVIOUSQUARTER(_today))Note that this doesn't work for your testfile as TODAY() will result in a date not available in your Calendar1[Date]. For testing, you can replace TODAY() with TODAY-4 (which evaluates to jan 2nd, as of this writing).
Let me know if this is what you were looking for 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂