Need help in writing calculated column

[manojk_pbi]

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 experince on DAX programming, so getting diffuclt to achieve this. I am not sure how to pick the previous month record and store. Below is the sample data Tabl1 and expected out put Tabl2 

Tabl1

MONTH_YEAR	FILTER_MONTH_YEAR_KEY	PROJECT_NAME	RELEASE_NAME	KPI
May-25	11624|2025-05-01	11624		0%
May-25	11624|2025-05-01	11624	Release A	99%
May-25	11624|2025-05-01	11624	Release B	71%
May-25	11624|2025-05-01	11624	Release C	83%
May-25	11624|2025-05-01	11624	Release D	59%
Jun-25	11624|2025-06-01	11624		0%
Jun-25	11624|2025-06-01	11624	Release A	89%
Jun-25	11624|2025-06-01	11624	Release B	65%
Jun-25	11624|2025-06-01	11624	Release C	85%
Jun-25	11624|2025-06-01	11624	Release D	60%
Jul-25	11624|2025-07-01	11624		0%
Jul-25	11624|2025-07-01	11624	Release A	78%
Jul-25	11624|2025-07-01	11624	Release B	71%
Jul-25	11624|2025-07-01	11624	Release C	65%
Jul-25	11624|2025-07-01	11624	Release D	59%
May-25	11763|2025-05-01	11763		47%
Jun-25	11763|2025-06-01	11763		79%
Jul-25	11763|2025-07-01	11763		79%

Tabl2

MONTH_YEAR	FILTER_MONTH_YEAR_KEY	PROJECT_NAME	RELEASE_NAME	KPI	PrevKPI
May-25	11624|2025-05-01	11624		0%
May-25	11624|2025-05-01	11624	Release A	99%
May-25	11624|2025-05-01	11624	Release B	71%
May-25	11624|2025-05-01	11624	Release C	83%
May-25	11624|2025-05-01	11624	Release D	59%
Jun-25	11624|2025-06-01	11624		0%	0%
Jun-25	11624|2025-06-01	11624	Release A	89%	99%
Jun-25	11624|2025-06-01	11624	Release B	65%	71%
Jun-25	11624|2025-06-01	11624	Release C	85%	83%
Jun-25	11624|2025-06-01	11624	Release D	60%	59%
Jul-25	11624|2025-07-01	11624		0%
Jul-25	11624|2025-07-01	11624	Release A	78%	89%
Jul-25	11624|2025-07-01	11624	Release B	71%	65%
Jul-25	11624|2025-07-01	11624	Release C	65%	85%
Jul-25	11624|2025-07-01	11624	Release D	59%	60%
May-25	11763|2025-05-01	11763		47%
Jun-25	11763|2025-06-01	11763		79%	47%
Jul-25	11763|2025-07-01	11763		79%	79%

[v-ssriganesh · Accepted solution]

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]

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

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]

Hello @v-ssriganesh ,

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

[v-ssriganesh]

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]

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%

[manojk_pbi]

Thanks @v-ssriganesh 

[manojk_pbi]

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

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