Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

Conditional formatting compared to the previous month

Good morning

I have a query: Is it possible to incorporate conditional formatting icons to an array that has as columns the months of management, rows a data "x" and as values the maximum of this same?

I am trying to perform this task but it always pulls me as BASE DATA of the values, the start of the time of the DATE field, when what I need is that the icons show the change with respect to the previous month:

dvalderaa_0-1647884378229.png

For example, in the row "AGNCIAS" for the month December and January 2021 you should have an AMBER icon, while for February a RED icon because it increased by 1 compared to January, then for March, return to AMBER because it remains with respect to February.

Then the GREEN icon should appear for everyone who shows a number less than the previous month.

Is it possible to perform that task? because as far as I have been able to search, it only allows me based on the beginning of the numbers in time.

Thanks a lot!

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Syndicate_Admin ,

     

    I have created a table for test:

    If you want to compare the value of current month and previous month, as set different icons for them, please try:

    Icon Measure = 
var _previous= CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[CANAL]=MAX('Table'[CANAL]) && FORMAT([Date],"yyyy-mm")= FORMAT(EOMONTH(MAX('Table'[Date]),-1),"yyyy-mm" )))
return SWITCH(TRUE(), SUM('Table'[Value])>_previous,"SignMedium", SUM('Table'[Value])=_previous,"SignLow","CircleHigh")

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Syndicate_Admin ,

     

    I have created a table for test:

    If you want to compare the value of current month and previous month, as set different icons for them, please try:

    Icon Measure = 
var _previous= CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[CANAL]=MAX('Table'[CANAL]) && FORMAT([Date],"yyyy-mm")= FORMAT(EOMONTH(MAX('Table'[Date]),-1),"yyyy-mm" )))
return SWITCH(TRUE(), SUM('Table'[Value])>_previous,"SignMedium", SUM('Table'[Value])=_previous,"SignLow","CircleHigh")

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      4 years ago

      Hello, Eyelyn!

      Thank you very much for your help, I have tried and the solution is valid. Just to tell you that I found another solution to get the month-to-month comparison that was through a quick measurement (month-to-month variation) and associating the dates to a calendar table, that is, I do not take as a basis the dates of my initial table. That helped me and now I can get the results from month to month:

      dvalderaa_0-1648159625224.png

      Best regards!!

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Administrator
        3 years ago

        Hello dvalderaa,

        Could you please share with me the formulation of the quick measure for the calculation, I am just wanting to do the same in a report and I could not.

        Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Syndicate_Admin ,

     

    Thanks for your feedback! ğŸ˜€

     

    It seems that your issue has been solved, please kindly Accept a reply as the solution to make the thread closed. More people will benefit from it.

     

     

    Best Regards,
    Eyelyn Qin