Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
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...
  • 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.

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
Icon for Administrator rankAdministrator
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
    Icon for Administrator rankAdministrator
    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.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      3 years ago

      Hello

      Esta formula use:

      Variacion_mensual =
      IF(
      ISFILTERED('Calendario_base'[Date]),
      ERROR("The rapid measure of time intelligence can only be grouped or filtered using the data hierarchy provided by Power BI or by the main data column."),
      Where __PREV_MONTH =
      CALCULATE(
      Max('Historical'[Q_prod]),
      DATEADD('Calendario_base'[Date].[Date], -1, MONTH)
      )
      RETURN
      DIVIDE(
      Max('Historical'[Q_prod]) - __PREV_MONTH,
      __PREV_MONTH
      )
      )
      • Syndicate_Admin's avatar
        Syndicate_Admin
        Icon for Administrator rankAdministrator
        3 years ago

        Many grasi, however not knowing the name of your fields in the model confuses me a little a couple of things, for example that this is for you ('Historical'[Q_prod]) and as you have formed the calendar table by the following 'Calendario_base'[Date].[ Date].

        Would there be a possibility to share perhaps the model to understand? or if you could make a small sample of it and share it in case maybe it is confidential data and you can not share it, it would really help me for a work report