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.

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%