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
        )
        )