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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
marcopaz
Frequent Visitor

Measure to Forecast based on average

Hello,

 

I am having a hard time to create a DAX formula that will create a projection of when a project will be completed based on how much is being Earned per month. I am trying to achieve something like the table below, where Power BI would detect the last month the Contractor earned something (2021 May), and then it would do two things:

1- Calculate the amount of months remaining at the given Average Earned so far

2- Project the Average Earnings for the remaining months.

 

marcopaz_0-1630443685663.jpeg

 

I had no issue on getting item 1, however I am still having a hard time to get the Average projected through the remaining months to complete.

 

Any ideas ?

 

Thanks,

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

1 create a date table, mark it as date table, and create a column of "YearMonthNumber" 

wdx223_Daniel_0-1630469829704.png

2 create a relationship between fact table and date table with date columns

wdx223_Daniel_1-1630469898229.png

3 create two measures

wdx223_Daniel_2-1630469958812.png

 

 

 

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

1 create a date table, mark it as date table, and create a column of "YearMonthNumber" 

wdx223_Daniel_0-1630469829704.png

2 create a relationship between fact table and date table with date columns

wdx223_Daniel_1-1630469898229.png

3 create two measures

wdx223_Daniel_2-1630469958812.png

 

 

 

Thank you very much Daniel, it worked perfectly ! I am still kind trying to understand the magic you've done to create the logic in which Power BI would project these values forward, but I am very happy with your solution.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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