Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add new column in Matrix visual with up or down arrow (comparison with previous month)

Hello Everyone,

Greetings,

 

i have created below matrix visual with with Year and month in column Region in rows 

 

 

i have to add new column after each month and which give the comparision of current month values with previous month values as shown below.

 

Green down Arrow   - Indicates Current month values is less compared to previous month

Yellow line - Indicates current month  values  are to previous month

Red Up Arrow -  Indicates Current month values is less compared to previous month

 

 

 

 

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Hope you have a dates table in your model and it is connected to your fact table. I used the Power BI sample data and created the expected results. The file is attached below my signature. 
    You need to create a measure to evaluate the status and assign it to the CF logic.

    DAX Formula to get the logic: 

    Growth = 
VAR __SALES = [Total Sales]
VAR __PM = 
    CALCULATE(
        [Total Sales],
        DATEADD( Dates[Date] , -1 , MONTH )
    )
    RETURN
IF(
    NOT( ISEMPTY(financials) ) && __PM <> BLANK() && HASONEVALUE(Dates[Year Month No]),
    SWITCH(
        TRUE(),
        __SALES  > __PM ,1,
        __SALES  = __PM, 0,
        __SALES < __PM,-1
    )
)



2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    Hope you have a dates table in your model and it is connected to your fact table. I used the Power BI sample data and created the expected results. The file is attached below my signature. 
    You need to create a measure to evaluate the status and assign it to the CF logic.

    DAX Formula to get the logic: 

    Growth = 
VAR __SALES = [Total Sales]
VAR __PM = 
    CALCULATE(
        [Total Sales],
        DATEADD( Dates[Date] , -1 , MONTH )
    )
    RETURN
IF(
    NOT( ISEMPTY(financials) ) && __PM <> BLANK() && HASONEVALUE(Dates[Year Month No]),
    SWITCH(
        TRUE(),
        __SALES  > __PM ,1,
        __SALES  = __PM, 0,
        __SALES < __PM,-1
    )
)



    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Fowmy Thanks the solution it is working for me