Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
1 year ago
Solved

Need help in writing calculated column

Hello Friends   I have below data in the table. The requirement is to write a DAX or MQuery to populate calculated column to copy the previous months KPI next to the KPI column. I don't have much e...
Need Help
Show and Tell
Tips and Tricks
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    Hello manojk_pbi,
    Thank you for reaching out to the Microsoft Fabric Community Forum.

    I have reproduced your scenario in Power BI Desktop using the sample data you provided. I implemented a DAX calculated column (PrevKPI) to copy the previous month’s KPI value for the same PROJECT_NAME and RELEASE_NAME and the output matches your expected result (Tabl2).

     

    For your reference, I’ve attached a .pbix file containing the solution. You can download it, open it in Power BI Desktop, and review the table to see the PrevKPI column in action. The DAX code used is as follows:

    PrevKPI =

VAR CurrentMonthYear = Tabl1[MONTH_YEAR]

VAR CurrentProject = Tabl1[PROJECT_NAME]

VAR CurrentRelease = Tabl1[RELEASE_NAME]

VAR CurrentDate =

    DATEVALUE(RIGHT(Tabl1[FILTER_MONTH_YEAR_KEY], 10))

VAR PrevMonthDate = EDATE(CurrentDate, -1)

VAR PrevKPI =

    CALCULATE(

        MAX(Tabl1[KPI]),

        FILTER(

            Tabl1,

            Tabl1[PROJECT_NAME] = CurrentProject &&

            Tabl1[RELEASE_NAME] = CurrentRelease &&

            DATEVALUE(RIGHT(Tabl1[FILTER_MONTH_YEAR_KEY], 10)) = PrevMonthDate

        )

    )

RETURN

    PrevKPI

    If you have any further questions, please don't hesitate to contact us through the community. We are happy to assist you.

    Best Regards,
    Ganesh singamshetty.

manojk_pbi's avatar
manojk_pbi
Helper V
1 year ago

v-ssriganesh , if i need to have difference of Current & Previous, how do we do it? Do i need to create one more column or can we directly do it in table chart ? 

How would DAX looks like ?

v-ssriganesh's avatar
v-ssriganesh
Community Support
1 year ago

Hello manojk_pbi,
Thank you for your follow-up.

To calculate the difference between current KPI and previous KPI (PrevKPI), you can use both new column and measure the column approach is recommended for storing the difference, while the measure suits dynamic visuals.

  • To Create a New Column:
KPIDifference = Tabl1[KPI] - Tabl1[PrevKPI]
  • Use a Measure in a Table Chart:
KPIDifference = CALCULATE(SUM(Tabl1[KPI]) - SUM(Tabl1[PrevKPI]), ALLEXCEPT(Tabl1, Tabl1[MONTH_YEAR], Tabl1[PROJECT_NAME], Tabl1[RELEASE_NAME]))

 

Best Regards,
Ganesh singamshetty.

  • manojk_pbi's avatar
    manojk_pbi
    Helper V
    1 year ago

    Hello v-ssriganesh ,

     

    Please can you help me with one more request, how can we add delta symbol to the difference value ?

     

     

    • v-ssriganesh's avatar
      v-ssriganesh
      Community Support
      1 year ago

      Hello manojk_pbi,
      Thank you for your request.

      To add the delta symbol (Δ) to the KPIDifference value, you can modify the DAX expression to concatenate the symbol with the difference. Since DAX doesn’t natively display symbols in numeric columns, you’ll need to format the result as text.

      • Modify the existing KPIDifference column with this DAX code:
      KPIDifference = "Δ " & FORMAT(Tabl1[KPI] - Tabl1[PrevKPI], "0.00%")
      • Modify the measure for use in a table chart:
      KPIDifference = "Δ " & FORMAT(CALCULATE(SUM(Tabl1[KPI]) - SUM(Tabl1[PrevKPI]), ALLEXCEPT(Tabl1, Tabl1[MONTH_YEAR], Tabl1[PROJECT_NAME], Tabl1[RELEASE_NAME])), "0.00%")



      Best Regards,
      Ganesh Singamshetty.

      • manojk_pbi's avatar
        manojk_pbi
        Helper V
        1 year ago

        Hi v-ssriganesh , 

         

        by concatenating symbol & value, we cann't color the value. What would be best approach if we need to have symbol and color the value based on the sign

        Δ -1.2% Δ 3.4%