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

    Hi v-ssriganesh , thanks for the solution. Great !.

    I will reachout to you in case of any further assistance required

  • 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.