Forum Discussion
Helper IISplitting contract amount over multiple years and months
OK done
I choosed another contract after checking the one you indicated to double check that it works but please make checks and I can fix in case further
See my result
The pbix modified is here
https://drive.google.com/drive/folders/1Yh9ltc_AdI0S1_zUcq7yvQnePHznNCij?usp=sharing
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Solution SageHi Zosy
My solutuion requires getting the monthly amount of the contract.
In Power Query, you will need to do a calculation.
Highlight the Months column, then Transform Tab > Scientific > Absolute Value this will remove the minus before the number. If you need to keep the data as is, then create another column.
Add a column and enter [Item Cost] / [Months]. Convert to Decimal Number
Load your data
Create a measure
VAR MaxDate =
MAX ( DIM_Date[Date] )
RETURN
CALCULATE (
[Contract Monthly Amount],
KEEPFILTERS ( 'YourTableName'[Contract Start] <= MaxDate
&& 'YourTableName'[Contract Expiry Date] >= MaxDate ),
ALL ( DIM_Date )
)
Add Year and Month columns from the DIM_Date table to a visual then the measure
Hope this helps
Joe