Create a cost center change flag from previous month

[Uniting_sampath]

Hello Power BI Users,

I have encountered an issue that requires creating a flag to identify instances of cost center changes compared to the previous month. Here's an example: Employee ID 1111 has transitioned from cost center 4000 to 5000 between Mar23 and Apr23. I need to generate a flag in the row where this change occurred.

[SamInogic · Accepted solution]

Hi,

As per our understanding, you are looking for a calculated Column that will return true if column cost center number value changes and employee values remains same ,

And also, you are looking to convert the excel formula to DAX expression for desired output column.

Please follow steps given below,

Initially, you have to create a Custom Column For Index from Power Query,

Then create a Calculated column for the Previous row Emp id using the below DAX Expression,

Previous row Emp id = 

LOOKUPVALUE('Table'[Emp id],'Table'[Index],'Table'[Index]-1)

Then create one more Calculated column for the Previous row cost center number using the below Dax Expression,

Previous row cost center no. = 

LOOKUPVALUE('Table'[cost center number],'Table'[Index],'Table'[Index]-1)

Finally, create the Desired output Column using the below DAX Expression,

Desired output = 

IF('Table'[cost center number]<>

'Table'[Previous row cost center no.] && 'Table'[Emp id]='Table'[Previous row Emp id],TRUE,FALSE)

If still above resolution does not meet to your requirement, help us by elaborating more about your question.

Thanks!

Inogic Professional Services Division

Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

Drop an email at crm@inogic.com

Services: http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/