Summarized Table showing all Dates between two dates

[DanielBI]

Hey guys, 

I am building a visual that is supposed to show expected future service revenues based on the deal value and a customer specified time window when the services should be executed. 

I have a table with all deals in it including the columns "deal id", "value", "first possible date" and "latest possible date". I now want to spread the "value" over all days between "first possible date" and "latest possible date".

I thought about making a new table using the summarize function that is supposed to show "deal id", "date"(all dates between first possible and latest possible for the respective deal) and "expected revenue"(value divided by number of days between).

I have tried using CALENDAR but some of the dates are empty, especially from the earlier days of the company, which yields an error.

How would you solve this problem?

http://community.powerbi.com/t5/Desktop/Getting-all-dates-between-2-dates/td-p/60577

(This does exactly what I need. It uses a query however. I don't think that is what I need here)

Thank you for your help!

[v-huizhn-msft · Accepted solution]

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