EFFICIENTLY creating a Summarised "byday" Table

Hi All

I could tackle this using measures but for various reasons I prefer to create a table in my Model, and do it using Dax. Here is a simple example table which shows a list of sales orders, the price of that sales order and the dates the service will be delivered

Sales Order From To Amount
SalesOrder1 15/01/2019 30/01/2019 50000
SalesOrder2 17/02/2019 25/02/2019 25000
SalesOrder3 15/05/2019 30/10/2019 3000
SalesOrder4 15/09/2019 30/10/2019 10000
SalesOrder5 15/03/2019 30/04/2019 6000
SalesOrder6 15/01/2019 02/02/2019 100000

I would like to arrive at this output in a table

SalesOrder Date Amount
SalesOrder1 15/01/2019 3,125
SalesOrder1 16/01/2019 3,125
SalesOrder1 17/01/2019 3,125
SalesOrder1 18/01/2019 3,125
SalesOrder1 19/01/2019 3,125
SalesOrder1 20/01/2019 3,125
SalesOrder1 21/01/2019 3,125
SalesOrder1 22/01/2019 3,125
SalesOrder1 23/01/2019 3,125
SalesOrder1 24/01/2019 3,125
SalesOrder1 25/01/2019 3,125
SalesOrder1 26/01/2019 3,125
SalesOrder1 27/01/2019 3,125
SalesOrder1 28/01/2019 3,125
SalesOrder1 29/01/2019 3,125
SalesOrder1 30/01/2019 3,125
SalesOrder2 17/02/2019 2,778
SalesOrder2 18/02/2019 2,778
SalesOrder2 19/02/2019 2,778
SalesOrder2 20/02/2019 2,778
SalesOrder2 21/02/2019 2,778
SalesOrder2 22/02/2019 2,778
SalesOrder2 23/02/2019 2,778
SalesOrder2 24/02/2019 2,778
SalesOrder2 25/02/2019 2,778
SalesOrder3 15/05/2019 18

I have tried the following (amongst other things) which demonstrates better what i try to do (I can figure out easy how to put amount in there so just focusing on getting dates)

    Test[Sales Order],Test[From],Test[To],

The key here is i dont want a Cross join table with every single date/Sales order combination possible as it will be huge.

I would be hugely grateful if anyone has a nice solution to this or let me know if something similar is in Sam’s resources that i perhaps missed.

Thanks in advance for reading


Since you are looking for a new table I went ahead and did this in power query. File is attached below, but here are the steps:

  • New Column that will produce a list of dates from the start to the end in each row
    Duration.Days([To] - [From]) +1,
    #duration( 1,0,0,0)
  • Group the rows by SalesOrder
  • Add a custom column to each sub-table that will give the daily price

“Avg Amt”,
Number.From([Amount]) /(Duration.Days([To] - [From]) +1)

  • Expand that table out
  • Remove columns no longer needed
  • Expand out the List of dates
  • Rename columns and set data types

Final Table:

PBIX file
PQ, Expand Summarize Table.pbix (11.8 KB)



Check this video.

If you wish to create the table in DAX, the PQ Solution, suggested by Nick is great too, use something like:

Answer = 
                AND (
                Calendar[Datum] >= Contracten[Contract From];
                Calendar[Datum] <= Contracten[Contract To]
"Actief Contract Nummer" ; Contracten[Contract Nr];
"Actief Contract Bedrag" ; Contracten[Contract bedrag per dag]



Hi Both

many thanks for the responses. this GENERATE function was surely what i was looking for. I got a bit lost in your formula Paul but a cut down version of it as per below is exactly what I was after

        date_table[date_value] >= Test[From] &&
        date_table[date_value] <= Test[To]

Once i got my data right I will surely move it over to the power query version suggested by you Nick so I have a single table output. Appreciate the file!

Thanks alot!!


Sorry about that, but figured you would see the pattern still. I have moved to Nicks solution too, need to see what happens with the # records growing, PQ is great for this.