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,

  • 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?

  • 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!