Comparing data by date

[KatkaS]

Hello

I have an excel sheet being updated every week and I would need to compare data of current week with the previous week.

e.g. below, I need to know how many requsitions were changed to status Closed... I'm looking for calculation where the result for Closed reqisitions (in below examples 2..)

Thank you very much!

Requisition number	Date	Status
ABC	6.8.2021	Approved
DEF	6.8.2021	Approved
GHI	6.8.2021	Approved
ABC	13.8.2021	Closed
DEF	13.8.2021	Closed
GHI	13.8.2021	Approved

[v-jingzhang · Accepted solution]

Hi @KatkaS 

Assume that once an item's status was changed to Closed, it would not change again, you can use below measure to calcuate the weekly number of requisitions that changed to Closed.

Measure = 
VAR __thisWeekCount = COUNTROWS(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date]) && 'Table'[Status]="Closed"))
VAR __lastWeekCount = COUNTROWS(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])-7 && 'Table'[Status]="Closed"))
RETURN
__thisWeekCount - __lastWeekCount

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[KatkaS]

THANK YOU VERY MUCH, JING!!!

[selimovd]

Hey @KatkaS ,

is the format of the values identical?

If yes, why don't you put all the Excel in one folder and load all of them with the load folder option? There you can merge all of the data into one table. Afterwards you can do you analysis in Power BI.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[KatkaS]

Hello Denis,

that's what I do already - I have on excel that is updated on weekly basis and I'm adding the new version to the folder which I then refresh in pbi..

but once it is uploaded I don't know how to achieve what I need (= calculate number of requisitions that changed status from Approved to Closed since last update..)

I hope I made myself clearer now..

Thank you!