month increment from selected slicer value to calc burn rate

[Swathi234]

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 

[v-cgao-msft · Accepted 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