Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
CracktheCode85
Helper II
Helper II

DAX Calculate Payments Per Day With 2 Data Sources

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)

CracktheCode85_0-1711481240589.png

 

Thank you for any help or guidance! 

1 ACCEPTED SOLUTION
adudani
Super User
Super User

hi @CracktheCode85 ,

 

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

adudani_0-1711490720595.png

 

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.

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

View solution in original post

1 REPLY 1
adudani
Super User
Super User

hi @CracktheCode85 ,

 

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

adudani_0-1711490720595.png

 

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.

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.