Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Niunias
Frequent Visitor

Freeze results from the historical data

Hello,

 

I really need to your help to find a solution, I am kind a fresh with PBI so I dont even know if this is possible.

 

I have sets of data, files are placed in the Sharepoint and every month new file is added.  I added a whole folder as my source in PBI because files have the same layouts every month. This is a short example of the data sets

 

Task      Employee           Check Start Date         Check End Date                 

Check     David Gahan    01/04/2021                05/04/2021

Check    Marc Almond     03/04/2021               07/04/2021

Check     Alan Wilder       06/04/2021               08/04/2021

Check     David Gahan    01/05/2021                05/05/2021

Check    Marc Almond     03/05/2021               07/05/2021

Check     Alan Wilder       06/05/2021               08/05/2021

 

In the other source I have a lists with employes, with an information to which team each employee is belonging to(I created reliationship between these two tabels)

 

Employee          Team

David Gahan     UK Team

Marc Almond    Global Team

Alan Wilder       US Team

 

I would like to measure how many "Check" there was in April and May but I need to exclude every "check" whenever it was done by someone from the Global Team. In result in my measure I will have 2 "Check" in April and 2 in May. But it is pretty often that list of employee is updated and for instance Marc Almond in April was in UK team but in May he was moved to Global team.

All what they do is changing the team name in this file so when I refresh the data right now, PBI will exlclude Marc Alomnd from every past months as well.

I refresh this data at the begining of every month. Is there any chance to "freeze" data from the past ? So PBI will only calculate measures with the "new file" ? Maybe there is a different solution to solve this problem ?  

 

I will appreciate anty help

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@Niunias , if this is simply based on Check Start Date  , you can have slicer for that

calculate(count(Table[Employee]), filter(Table2, Table2[Team] <> "Global Team"))

 

if is based on active between start and end, then refer to this blog  and use team <>

 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

I know how to build measure like this, but the thing is that in one month Marc Almond in employee file is in UK team but in other month Marc Almond in the same employee files is  in global team, so when I refresh it, Marc Almond will not be counted in the previous months as well but I would like to keep results from previous months when Marc Almond was not a part of the global team

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.