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,

 

ORION007's avatar
ORION007
Regular Visitor
4 years ago

Hi!

 

Thank you so much for your post - it was of a great help!
Just one more thing, is there a way to make the spread throughout the months flat? Instead of calculating the volume per month by multipling the days, we have the entire year volume just divided by 12 and than, only showing this "flat volume" at the intersection of the months between "Order date" and "Delivery Date"?

Once again, thank you so much for your kind help!