Calculations in history

[bdeleur]

Hi everybody,

In a database I do a calculation for now to see if the date is overdue. Realtime works perfectly but I'm wondering if I can recreated the same outcome but in history.  If I selectef 1-1-2023 I want the calculation of that day etc etc.

The rule in Powerquery is now:

#"OLL?" = Table.AddColumn(#"Aangepaste kolom toegevoegd13", "OLL?", each if [RealizedDischargeDate] = null and [Inititialdischargedate_aanpassing_WLZ] < Date.From(DateTime.LocalNow()) and ([DischargeProposedStartDate]= null or [DischargeProposedStartDate] > Date.From(DateTime.LocalNow())) then "OLL" else null),

I hope somebody can help me.

Greetings

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[bdeleur]

Hi,

now I make an excel file every time the dashboard updates. I don't have any clue how to make the same logic work for an archive.

[lbendlin]

Power BI has no memory* . You need to manage your snapshots in the upstream system. * Yes, there is the self referencing option but that has no safety net.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[bdeleur]

I'm struggling to create the database for here. Hopefully the tables below will help.

The intention is to check for each day whether a row (ID) falls between a start date and an end date. The intention is that only the COUNT is reported. The other columns are for the example.

	ID	ID	ID	COUNT
	1	2	3
1-1-2023	X			1
2-1-2023	X	X		2
3-1-2023	X	X		2
4-1-2023		X		1
5-1-2023		X		1
6-1-2023		X	X	2
7-1-2023			X	1
8-1-2023			X	1

ID	startdate	enddate
1	30-12-2022	3-1-2023
2	2-1-2023	6-1-2023
3	6-1-2023

[lbendlin]

see attached

[bdeleur]

Your solution indeed works perfectly on the sample data but somehow not on my real dataset. That's why I have a bit more extensive set and outcome here that I can share with you. Hopefully you see what happened.

The screeshot is the result of your code:

Expected outcome 

Data 

[lbendlin]

see attached

[bdeleur]

Hi, thanks for the new file. That actually worked for me too. What went wrong for me is that I used my date table and not a separate column with the date. Now it fits better, but that also makes me discover that it will never work since the data is always adjusted to what I want to see. The status of the IDs change, but that is not recorded in a database, the data is always overwritten.

Thank you for your help, it was certainly correct and fast.
Thanks, I'll mark your answer as resolved.

[bdeleur]

Hi @lbendlin,

Sorry I was on holiday. I'll make a sample dataset and post it here. To be continued. Tnx for the help.