Calculating Status change with respect to date

[Khgo]

Dear Experts,

I'm trying to solve an issue. i would really appreciate yours help 🙂

I have a table and it has 3 Columns, i.e. Btach number, Flowstep, Timestamp for flowstep change

I want to calculate step difference whenever it changes and then compare it with Last week and Current week.

Our week start from Tuesday and ends on Monday. so it should sum total number of step changes in the last week and compare with current week.

I hope you can help me out 🙂

Regards,

Khurram

[eliasayyy]

@Khgo this is the batch from your example

[Khgo]

Yes, that seems correct now 🙂

could you pleas send me and i let you know. the other PBI file calculation was based on sum and wasn't corrcet for me.

[eliasayyy]

@Khgo 

KHGO.pbix

[Khgo]

I htink we are getting closer. just need a bit corrcetion in your latest PBI. if you look at the pic below, the difference should be 1, for each instance changed.

the one highlighted in yellow should be one. and then the sum should be 9 as you can see in my report .

If somehow you can calculate the sum of total changes that would be awesome

[eliasayyy]

i sent you an updated file KHGO.pbix here it is again 

anotehr way is to use a new measure

Total = 
IF(HASONEVALUE('KHGO'[Batch Number]),[Flow Variation] , SUMX(VALUES('KHGO'[Batch Number]),[Flow Variation]))

[eliasayyy]

@Khgo remember if you use dont summarize then of course you wont get a total how can you get a total if you set the column to dont summarize you need to set it as sum or something

[Khgo]

I don't know if there's an issue, but it wont let me paste my data as a table here. 😞

[eliasayyy]

its ok copy it to excel and share the excel file via dropbox or one drive or google drive link

[Khgo]

Please see if that works 🙂

[Khgo]

https://drive.google.com/file/d/1Er3m5liElMsxNU6tV7DypeySLzg1deBY/view?usp=sharing 

[eliasayyy]

ok im not sure if i got what you want correctly 

is this the result? if yes let me know to share with you the necessary steps

[Khgo]

no no, i need something different. let say, you have calculated this week steps and previous week steps. if you can create another column mentioning their difference with respect to date.

for ex: for batch xyz, we were at  step no 4 on 26-08-2023 and today on 31-08-2023, it changed to step no. 5, so the new column should show the difference i.e. 1 (5-4 = 1)  and that should be done on week level.

Could you also please not use sum cz it will complicate it a bit 🙂

sorry i asked for sum before 🙂

[eliasayyy]

ok i am starting to understand but how will you do it without sum? 

if you notice on thursday you have multiple values for batch 123005 so what to do in this case?

and do you need day by day till the week selected correctly?

[Khgo]

If it's possible to do day by day then it'll be good but weekly is the one I need. So let say, you create a card and add in there steps from last week and in another card total steps in this week and on the third card we can show total steps change from last week (current week steps - Last week steps)

[eliasayyy]

soyou need the first solution i showed you but just add the difference right? and you need them in cards with batch filter slicer?

[Khgo]

Yes, your solution is nice, just want to have difference whenever step changes in another column without the sum of them. and on the card, just sum the diffrence and on another card, total batches where the difference happened and total batches where no difference happened.

[eliasayyy]

is this the result you are looking for?

[Khgo]

Just want to show you how i did that but i'm unable to do a sum on the total steps moved in a week. it shows me total numbe rof steps as 11 which is incorrect.

i used this formulae to calculate the difference,

Flow variation = Var Diff = 'Release Flow Status'[FlowStep] - CALCULATE(SUM('Release Flow Status'[FlowStep]), FILTER('Release Flow Status', 'Release Flow Status'[Index.1] = EARLIER('Release Flow Status'[Index])))

return

if(Diff = VALUE('Release Flow Status'[FlowStep]), 0, Diff)

[eliasayyy]

mmm ok how did you add index you added index to each batch? if its possible to share you pbix file

[Khgo]

Yes, I just made two index columns, in power query, one starting with 1 (index.1), and second column starting with 0- (index)