Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
i have below scenario , date and fact are join on fin_period date.date = fact.fin_period
DEPT | Project | Fin_period | Amount | Forecast_type | Exe_date |
A | xyz | 20221101 | 5000 | Budget | 20230301 |
A | xyz | 20230301 | 2000 | Budget | 20230301 |
A | xyz | 20230501 | 1000 | Budget | 20230301 |
needed output
Slicer: date dimension -> year and month
YTD amount | Burn rate | |||||||
Slicer selected month | 20221101 | XYZ | 5000 | project not exe | ||||
20230201 | XYZ | 5000 | project not exe | |||||
20230301 | XYZ | 7000 | 7000 | first month of execution | ||||
20230401 | XYZ | 7000 | 3500 | Second month of execution | ||||
20230501 | XYZ | 8000 | 2666.667 | divide 3 (3 month from exe) | ||||
20230601 | XYZ | 8000 | 2000 | divide 4 (4th from exe date) |
banging my head to find the increment value from the exedate and calc burn rate , i have to check these values each project on each FY based
can any one help
Solved! Go to Solution.
Hi @Swathi234 ,
Please try:
YTD =
CALCULATE(
SUM('fact'[Amount]),
FILTER(ALL('fact'),'fact'[Fin_period] <= MAX('date'[date]))
) -
CALCULATE(
SUM('fact'[Amount]),
FILTER(ALL('fact'),'fact'[Exe_date] <= MAX('date'[date])),
USERELATIONSHIP('date'[date],'fact'[Exe_date])
)
Burn rate = DIVIDE([YTD],MAX('date'[Month Number]) - 1)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @Swathi234 ,
Please try:
YTD =
CALCULATE(
SUM('fact'[Amount]),
FILTER(ALL('fact'),'fact'[Fin_period] <= MAX('date'[date]))
) -
CALCULATE(
SUM('fact'[Amount]),
FILTER(ALL('fact'),'fact'[Exe_date] <= MAX('date'[date])),
USERELATIONSHIP('date'[date],'fact'[Exe_date])
)
Burn rate = DIVIDE([YTD],MAX('date'[Month Number]) - 1)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
93 | |
88 | |
35 | |
35 |
User | Count |
---|---|
154 | |
101 | |
80 | |
63 | |
54 |