DAX Calculate Payments Per Day With 2 Data Sources

[CracktheCode85]

Hello,

I have two data sources I am building a dashboard with. 
1st source is directly tied to a SQL database. 

2nd source is a static file with manually created for Working Bank Days in 2023 and 2024 only. 

I created a relationship between these two sources to get accurate visuals as the data is aggregated to Working Days. 

All other visuals work and match up perfectly. 

This one however is giving me a difficult time. 

I need the average of the payments up to the most current working day. 

However when I created a running total or an average measure for payment I get wrong numbers. 

Here is an example of what I'm trying to accomplish so that "Cash Up To Day 05" will change as the month moves forward in Working Days (Tomorrow will be "Cash Up To Day 06" and so forth)

Thank you for any help or guidance! 

[adudani · Accepted solution]

hi @CracktheCode85 ,

For a static rolling period : How to Calculate Moving Averages in Power BI - The Ultimate Guide (youtube.com)

in the measure above, after creating a measure that counts the number of days (current date - start of month date +1 ) , try and subsitute the static period definition in the variable (-3 in the picture above).

let me know if this makes it a dynamic rolling period.