Forum Discussion
Helper IIIDynamically representing current and past months in a table
Hi,
I have a page with a column chart and table which looks like this:
What I am trying to do is represent last months RAG status' in the table in the column next to RAG Status (Actions RAG M-1). Where the difficulties lie is having this dynamically change as you click through the column chart above - so when you click on February, the table will show the RAG status' for the Activities reported on in February but also show what the RAG status' were for those Activities in January - in the Actions RAG M-1 column .
I have a dynamic Month Index column (I.e., an index which represents the current month as 0, previous month -1 etc.) and have tried to use this in a measure, but so far I can only get the current months previous month RAG status.
Example dataset for a single Activity and pre-populated RAG M-1:
| Title | Account Name | Activity ID | RAG | RAG M-1 | Export Date | Month Index |
| AA | BB | 123 | Red | null | 01/01/2021 | -4 |
| AA | BB | 123 | Amber | Red | 01/02/2021 | -3 |
| AA | BB | 123 | Green | Amber | 01/03/2021 | -2 |
| AA | BB | 123 | Red | Green | 01/04/2021 | -1 |
| AA | BB | 123 | Amber | Red | 01/05/2021 | 0 |
| CC | DD | 321 | Amber | null | 01/01/2021 | -4 |
| CC | DD | 321 | Green | Amber | 01/02/2021 | -3 |
| CC | DD | 321 | Red | Green | 01/03/2021 | -2 |
| CC | DD | 321 | Amber | Red | 01/04/2021 | -1 |
| CC | DD | 321 | Green | Amber | 01/05/2021 | 0 |
The RAG M-1 column should be populated using a measure as explained above, it is pre populated to demonstrate the desired outcome.
Hope all that makes sense?
Thanks,
Dean
deanbland Seems like in your measure you could do something like:
Measure = VAR __Current = MAX('Table'[Export Date]) VAR __TwoMonthsAgo = EOMONTH(__Current,-2) VAR __LastMonth = MAXX(FILTER(ALL('Table'),[Export Date]>__TwoMonthsAgo),[Export Date]) RETURN MAXX(FILTER(ALL('Table',[Export Date]=__LastMonth),[RAG M-1])Something like that, you will have to add in additional filters for things like Account, etc.
3 Replies
- Greg_Deckler
Community Champion
deanbland Seems like in your measure you could do something like:
Measure = VAR __Current = MAX('Table'[Export Date]) VAR __TwoMonthsAgo = EOMONTH(__Current,-2) VAR __LastMonth = MAXX(FILTER(ALL('Table'),[Export Date]>__TwoMonthsAgo),[Export Date]) RETURN MAXX(FILTER(ALL('Table',[Export Date]=__LastMonth),[RAG M-1])Something like that, you will have to add in additional filters for things like Account, etc.
- Greg_Deckler
deanbland Do you really not have real date or a Month number or a Year involved in any of this? Otherwise you would be looking a big SWITCH statement where you check the value of the month selected and return the previous month like IF "February", return "January".
deanblandGreg_Deckler Hi Greg, apologies - I have updated the example data set to more acurately show what my data looks like.