Summarized Table showing all Dates between two dates

Hi DanielBI,

I create the following sample data and try to reproduce your scenario. Please refer the steps below.



1. Create a calculated column to get the diff between "first possible date" and "latest possible date".

diff = DATEDIFF( CALCULATE(MIN(Table3[firts possibe data]),ALLEXCEPT(Table3,Table3[deal id])),CALCULATE(MAX(Table3[last possibe data]),ALLEXCEPT(Table3,Table3[deal id])),DAY)

2. Cretae a new table by clicking "New table" under Modeling on home page. In the table, calculate the value that spreads the "value" over all days between "first possible date" and "latest possible date".

Table = DISTINCT(SELECTCOLUMNS(Table3,"ideal id",Table3[deal id],"value",Table3[value],"first",Table3[firts possibe data],"Diff",Table3[diff]))

Column = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[ideal id]))/'Table'[Diff]

3. Add a custom column like the thread. Expend it, you will get the result shown in second screenshot.

={Number.From([firts possibe data]),Number.From([last possibe data])}

4. Create new table to get the expected result.

Table 3 = CROSSJOIN(DISTINCT(SELECTCOLUMNS('Table',"ideal id",'Table'[ideal id],"Expected revenue",'Table'[Column])),SELECTCOLUMNS(Table3,"ideal id1",Table3[deal id],"Date1",Table3[Custom]))

Table 4 = DISTINCT(FILTER('Table 3','Table 3'[ideal id]='Table 3'[ideal id1]))

Finally, delete one ideal id column.

Table 5 = SELECTCOLUMNS('Table 4',"ideal id",'Table 4'[ideal id],"Date",'Table 4'[Date1],"expected revenue",'Table 4'[Expected revenue])

If you have other issues, please feel free to ask.

Best Regards,

Angelia