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,

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-lid-msft 

 

Thanks a lot for this solution, I managed to apply it on my Power BI.

Just two questions :

- What is your first picture about ? what are TAB_Litig_File and TAB_Prov ?

- Would it be possible to have a column with every amount for every month for each project ? I need this information to create another Matrix Table, in order to compare with my maximum production capacity.

 

Thanks again for your help.

 

Nicho

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Sorry for that we put an unnessary screenshot, we selected wrong file when upload, it does not help in this post.

     

    What is the column with every amount, Could you please share a mockup table based on the tables you have shared?


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,