Hi there,
I'm converting an Excel sheet to something a bit more interactive with PowerBI and Sharepoint and am stuck on calculation that I can't seem to figure out.
From the sheet headers, I have:
Date Predicted True Change Level
The date columns has rows Monday to Sunday; Predicted for Monday does not exist, and the rest of the rows are calculated from (Level + Change); True and Change are manually recorded for each day; Level is manually entered for Monday, and the rest of the days are calculated as being equal to Predicted unless True is not blank, then True is used.
The way the sheet calculates Predicted and Level, they reference each other, but on different days.
Ex.
Tuesday
-> Predicted is calculated using Change and Level for Monday
-> Level then uses the calculated Predicted value (unless True is not blank)
Wednesday
-> Predicted is calculated using Change and Level for Tuesday
-> Level then uses calculated Predicted value (unless True is not blank)
This continues for the rest of the days in the week, etc.
Would anyone know of a way to do this in PowerBI? I made some progress using DAX but kept running into cyclical reference errors and couldn't figure out a way to not calculate subsequent values until the previous days calculation took place as above (and saw mention that recursion is not easily done with this). I then tried doing it using Power Query but not getting very far either.
I've also though maybe to use something like a combination of Sharepoint and Power Automate but haven't gone that route yet.
Thank you and help is appreciated!
