Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
deanbland
Helper III
Helper III

Dynamically representing current and past months in a table

Hi, 

 

I have a page with a column chart and table which looks like this: 

 

deanbland_1-1621440095030.png

 

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: 

 

TitleAccount NameActivity IDRAGRAG M-1Export DateMonth Index
AABB123Rednull01/01/2021-4
AABB123AmberRed01/02/2021-3
AABB123GreenAmber01/03/2021-2
AABB123RedGreen01/04/2021-1
AABB123AmberRed01/05/20210
CCDD321Ambernull01/01/2021-4
CCDD321GreenAmber01/02/2021-3
CCDD321RedGreen01/03/2021-2
CCDD321AmberRed01/04/2021-1
CCDD321GreenAmber01/05/20210

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

 

1 ACCEPTED SOLUTION
Greg_Deckler
Community Champion
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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Community Champion
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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Community Champion
Community Champion

@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".

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Hi Greg, apologies - I have updated the example data set to more acurately show what my data looks like. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors