Use end of the day value as input to start the next day

[StroopWafel88]

Hi all, 

I have been searching the forum/community but I could not really find the solution I am looking for. Below I have described to my best what my problem is and I hope anyone can help. 

Thing is I have a simple table overview that I want to visualise with a date column, a type column and then a couple of columns that show the following: 

- New e-mail's of today - From a table with only totals per type of inbox folder without an ID 

- Handled e-mail's of today - From another table with only totals per type of inbox folder without an ID

- End of the day (today) total - basically a calculated column by substracting the new e-mails minus the handled e-mails. 

The end of the day total (today) is the start amount of the next day topped with the new e-mail's total of that next day. 

Basically this is were the trouble starts for me. I tried to do the following, use DATEADD minus 1 day to get the End of the day total (today) to use for the start of tomorrow. We then have:

- End of the day yesterday (which is the input for the next morning)

- New e-mail's of today (topped on the end of the day of yesterday, making a start total of today)

- Handled e-mail's (which I substract of the Start total) 
- End of the day total today (which is the start total minus handled). 

At this point Power BI gives me the circular dependency error, which I understand, because I'm trying to make the end of the day today to be the input of yesterday by date. But with errors the're workarounds and I'm looking for something to make this work. 

Below is some example data and to explain it a little better:

What you see is that the most right column of 06-03-2023 is the first column of the next day (07-03-2023). 

In power BI I am using a Slicer on top of the page to select each day you want to view, so I can decide to view 6 or 7 March, and switch back and forward. Also in my measure (with DATEADD-1) to get yesterday's value of end of day, I used a little VAR logic for the weekends, basically saying if Day of the week is 1, -3 (for Friday) otherwise -1 (for all other working days). Now in this logic it only looks at the previous day but I want to see the SUM of those end of days versus handled in total (what I call the amount of e-mail's that need to be handled). 

Since the handled and new in e-mails are coming from 2 tables, I merged them into 1 table (in Power Query) and that table is shown above. Having a many to one relationship to a Calander Dimension table (on Date). 

Hope you can help me out making this work, thank you once more. 

[AnthNC]

Hi! 

Same problem here, can anyone help please ?

[StroopWafel88]

@amitchandak @Greg_Deckler Can anyone help me please? 

[StroopWafel88]

Anyone who can help me?🙄