Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to spread value between two dates ?

Hi everyone,   I need some help on my Power BI.   I am trying to spread the value of a project over several months, to know exactly how much I will have to produce every month. For exemple, if I...
Need Help
Show and Tell
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can create a calculated table and a measure to meet your requriement:

 

Calculated Table:

 

 

DateTable = CALENDAR(MIN('Table'[Order Date]),MAX('Table'[Delivery]))

 

 

Measure:

 

 

MonthAmount = SUMX(DISTINCT('Table'[Project]),
CALCULATE(SUM ( 'Table'[Amount] ))
    / DATEDIFF ( MIN ( 'Table'[Order Date] ), MAX ( 'Table'[Delivery] ), DAY )
    * COUNTROWS(INTERSECT('DateTable',CALENDAR(MIN('Table'[Order Date])+1,MAX('Table'[Delivery])))))

 

 

 


Best regards,

 

thomasgee's avatar
thomasgee
Regular Visitor
4 years ago

This solution is really useful for my use case too. I don't suppose you would mind taking the time to explain how it works? Or link to the material where you got this from yourself?