EFFICIENTLY creating a Summarised "byday" Table

Power BI Data Model
1

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)

SUMMARIZECOLUMNS(
    Test[Sales Order],Test[From],Test[To],
    "Series",GENERATESERIES(MAX(Test[From]),MAX(Test[To]),1)
)

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

James

2

James,
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
List.Dates(
    [From],
    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

Table.AddColumn(
[Data],
“Avg Amt”,
each
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:

Final%20Table
Final%20Table508×656 35 KB

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

Nick

Enterprise%20DNA%20Expert%20-%20Small

3

Hi,
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 = 
SELECTCOLUMNS(
    GENERATE(.....;FILTER)
                ALLNOBLANKROW(Calendar);
                AND (
                Calendar[Datum] >= Contracten[Contract From];
                Calendar[Datum] <= Contracten[Contract To]
                ))
    );
"Datum";Kalender[Datum];
"Actief Contract Nummer" ; Contracten[Contract Nr];
"Actief Contract Bedrag" ; Contracten[Contract bedrag per dag]
)

Paul

Enterprise%20DNA%20Expert%20-%20Small

4

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

GENERATE(
    Test,
    FILTER(
        VALUES(date_table[date_value]),
        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!!

James

5

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.

Paul

Enterprise%20DNA%20Expert%20-%20Small