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! 🙂
Resident RockstarALright 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! 🙂
Resident RockstarSorry 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! 🙂
socksinboxHi Thanks for this and I knew this could be achieveable directly in measure but imagine if you have multiple numeric field in your table. Is it not good that instead of creating a lastquarter measure for all, just create a 'measure filters and filter and keep that on the page filter. This way if business comes and ask to show last month or something else then we only need to change the measure filter.
I recall I used to do this in Tableau and it's very easy to filter dimension with calculated measure value instead of a an static valuesAnyways, thanks for the below.
LastQ = VAR _today = FILTER(VALUES(Calendar1[Date]), Calendar1[Date] = TODAY()) RETURN CALCULATE(SUM('Customer Score'[CustomerScore]), PREVIOUSQUARTER(_today))Just wanted to ask onething. Why you need to filter calendar table with Today() ? Can't we directly pass Today() in PreviousQuarter function?
- JarroVGIT
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! 🙂
- socksinbox
Thanks JarroVGIT. I am trying to create this measure but it's not working- give me below error.
https://i.imgur.com/GPiGDPn.png
Appreciate if you can do in the test file I shared