HELP! net change in balances day over day in Power BI

[durha_]

Hello all, @pbissue 

I'm working on a project in Power BI that requires a line chart that will calculate the daily balance (based off of a date, 'Daily CD GL Balances[Report Date] (the report date has an inactive relationship with the Calendar table, so a USERELATIONSHIP() function would be required)) my overall goal is to have the line chart list the net change in balances from the 1st of the month to the current date. So if on the 1st, the balance is 100,000 and on the 2nd the balance is 75,000, I want this to show on the graph as (25,000) or if the balance is 100,000 on the 1st and 500,000 on the 15th, I want the line chart to show +400,000 and so on. I want the user to be able to choose the year, month, and range of days to look at. Some users may want to look at the month as a whole, other users may want to look at a specific range. This is just a quick overview. Please ask any questions, or let me know if I need to clarify something.

I appreciate the help.

Deja

[Kedar_Pande · Accepted solution]

@durha_ 

FirstDayBalance = 
CALCULATE(
SUM('Daily CD GL Balances'[Balance]),
USERELATIONSHIP('Daily CD GL Balances'[Report Date], 'Calendar'[Date]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] = DATE(YEAR(MIN('Calendar'[Date])), MONTH(MIN('Calendar'[Date])), 1)
)
)

DailyBalance = 
CALCULATE(
SUM('Daily CD GL Balances'[Balance]),
USERELATIONSHIP('Daily CD GL Balances'[Report Date], 'Calendar'[Date])
)

NetChange = 
DailyBalance - FirstDayBalance

Create the above 3 measures.

X-axis: Use the Calendar[Date] field to show dates.
Y-axis: Place the NetChange measure to display the daily net change from the 1st of the month.
Slicers: Add slicers for year, month, and date range from the Calendar table, allowing users to filter the date range dynamically.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[durha_]

This worked. Thank you!! I was missing the ALL() function in my formula.

[danextian]

Hi @durha_ 

You can use PREVIOUSDAY to get the previous day's value with a separate dates table that's been marked as a date table.  Here's a sample DAX measure

Sales Prev Day = 
CALCULATE([Sales USERELATIONSHIP], PREVIOUSDAY(Data[Date]))

Please see attached sample pbix.