Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Display Last 3 Reporting Period Column Text

I have a table called ReportPeriod as shown below:

 

I want to display last 3 month RAG value which is text in RAG Column.

For example, 20/03/2022 is the current month, hence the current RAG is Amber when company A is selected on the Slicer and the Month Slicer.

So I want to get the last 3 month RAG value for Company A which is Amber.

last 3 month RAG value for Company B which is Red.

last 3 month RAG value for Company C which is Amber.

How can I achieve this because I want to display it on a Card visual

16 Replies

  • Hello,

     

     

    Step 1. 

    Max Month = CALCULATE(MAX(( Sheet16[ReportPeriod])), ALL(Sheet16), Sheet16[Company]=MAX(Sheet16[Company]))
     
    STEP2.
    DateDiff = CALCULATE(DATEDIFF(max(Sheet16[ReportPeriod]), [Max Month],MONTH))
     
    STEP3.
    Last 3 Month = IF([DateDiff]=3,MAX(Sheet16[RAG]))
     

     

    Regards,

    Ritesh

    Please mark the answer if helpful so that it can help others as well

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      ribisht17 - Thanks for the reply. However, I wanted to avoid creating Calculated Column but to use measure.

      I followed the steps you gave by using variable in a measure show below:

      Last 3 month = 
      var MaxMonth = CALCULATE(MAX(( Sheet16[ReportPeriod])), ALL(Sheet16), Sheet16[Company]=MAX(Sheet16[Company]))
      var DateDiff = CALCULATE(DATEDIFF(max(Sheet16[ReportPeriod]), [Max Month],MONTH))
      RETURN
      IF([DateDiff]=3,MAX(Sheet16[RAG]))


      I get error stating "A function 'MAX' has been used in a True/False that is used as a table filter expression. This is not allowed"
      Please help

  • Here is the Card Pic

     

    NOTE: Filter Last 3 months as NOT BLANK

     

     

    Regards,

    Ritesh

    Please mark the answer if helpful so that it can help others as well

     

  • I have used Measures only

     

    Can you share your data/pbix?

     

    Thanks,

    Ritesh

    • Anonymous's avatar
      Anonymous
      Not applicable

      I can't share pbix because I am using organisation account which doesn't allow me due to company policies.
      Hence, I have add screenshot of my data model for test data.

      company ReportPeriod RAG
      A 25/12/2021 Amber
      A 22/01/2022 Green
      A 19/02/2022 Red
      A 20/03/2022 Amber
      B 25/12/2021 Red
      B 22/01/2022 Red
      B 19/02/2022 Green
      B 20/03/2022 Amber
      C 25/12/2021 Amber
      C 22/01/2022 Green
      C 19/02/2022 Red
      C 20/03/2022 Amber

      Can you share your pbix please.?

      When I remove Max, I still get error.

  • What happens if you remove max here 

    old

    var MaxMonth = CALCULATE(MAX(( Sheet16[ReportPeriod])), ALL(Sheet16), Sheet16[Company]=MAX(Sheet16[Company]))

     

     new

    var MaxMonth = CALCULATE(MAX(( Sheet16[ReportPeriod])), ALL(Sheet16), Sheet16[Company]=(Sheet16[Company]))

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      ribisht17 Thanks for your reply.
      It works on Table visual and multi-row card but not on card visual.

      see screenshot below.

      There a slicer that Company slicer and reportingdate period slicer and this didn't work on card visual.

  • Please find the solution in the same drive Here is the file PowerBI Community - Google Drive

    Check the Filter for this slicer

     

     

     

     

    Regards,

    Ritesh

    Please mark the answer if helpful so that it can help others as well

     

     

  • Hi Ealtim,

     

    Did it help you ? Please mark the answer as solution if helpful so that it can help others as well

     

    Regards,

    Ritesh

    • Anonymous's avatar
      Anonymous
      Not applicable

      Ashish_Mathur . Thanks for the reply. But your pbix shows only last month. 
      What I want to achieve is to get RAG colour for previous 3 month. 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User

        So what exact result are you expecting in the card visual if 2021 and A are selected in the slicers.

  • Thanks for marking the solution EalTim 🙂

     

    Regards,

    Ritesh