topic Freeze results from the historical data in DAX Commands and Tips

Freeze results from the historical data

Niunias — Tue, 01 Jun 2021 10:03:40 GMT

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

Re: Freeze results from the historical data

amitchandak — Tue, 01 Jun 2021 11:24:10 GMT

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-trend/ba-p/882970

Re: Freeze results from the historical data

Niunias — Tue, 01 Jun 2021 13:14:31 GMT

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