Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Team,
I am new to Power BI and DAX. I had been assigned to convert an Excel report into Power BI which has the following calculations.
I am stuck up with the calculations for Behind , which is based on the excel formula , Prior Behind Days + ( Target Days - Rolling Average Days) / Weeks left. Behind Days for Week 1 is 0 .
For .e,g, for Week 3, the behind is calculated as 0.5 + (350 - 295.67)/49 = 1.6,
Can you please help ?
Cheers,
Andy
Solved! Go to Solution.
Hi @andy291086 ,
I created a sample pbix file(see the attachment) for you, please check if that is what you want.
Week Number = VALUE(RIGHT('Table'[Week], LEN('Table'[Week]) - 5))
Column =
VAR _preweek =
CALCULATE (
MAX ( 'Table'[Week Number] ),
FILTER ( 'Table', 'Table'[Week Number] < EARLIER ( 'Table'[Week Number] ) )
)
VAR _prewbdays =
CALCULATE (
MAX ( 'Table'[Behind Days] ),
FILTER ( 'Table', 'Table'[Week Number] = _preweek )
)
RETURN
_prewbdays + ( 'Table'[Target Days] - [Rolling Average - Days] ) / 'Table'[Weeks Left]
Best Regards
Hi @andy291086 ,
I created a sample pbix file(see the attachment) for you, please check if that is what you want.
Week Number = VALUE(RIGHT('Table'[Week], LEN('Table'[Week]) - 5))
Column =
VAR _preweek =
CALCULATE (
MAX ( 'Table'[Week Number] ),
FILTER ( 'Table', 'Table'[Week Number] < EARLIER ( 'Table'[Week Number] ) )
)
VAR _prewbdays =
CALCULATE (
MAX ( 'Table'[Behind Days] ),
FILTER ( 'Table', 'Table'[Week Number] = _preweek )
)
RETURN
_prewbdays + ( 'Table'[Target Days] - [Rolling Average - Days] ) / 'Table'[Weeks Left]
Best Regards
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |