Distribute dates between start and end date in Direct Query

[Anonymous]

Hi all,

I have a question with which I am struggling for some days already. I have a Power BI dataset which is connected to Dataverse via the Azure SQL connection. I am using Direct Query as the connection type because the client needs to see data in real-time.

So far, everything is working fine but there now is a new requirement which is about displaying projects with a start and end date on a timeline (they have a capacity which should be shown for every date the project is going).

I have a table which is structured similarly to the following model:

Now, the goal is to show data in the following format so that I can connect it to the existing date table:

So far, I have been using approaches similar to the following:

• {[Start Date]..[End Date]} in Power Query -> see e.g. https://www.thepoweruser.com/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/ 
• Using a new calculated table which generates a new table based on project table and on a date table (using GENERATE and SELECTCOLUMNS) -> see https://community.powerbi.com/t5/Desktop/chart-with-start-and-end-date/m-p/484219/highlight/true#M22...  

However, since I am using Direct Query now, there is no option for me to create a custom table and I can also not create a custom column in Power Query as I did in other projects.

Does anybody have an idea on how to accomplish that? The data source unfortunately does only provide the format of the first image.

Would be great if you could help me with that. I appreciate any answer!

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

It is no possible to create a custom table in DirectQuery mode, but you can create a table during calculation, like summarize().

Expression you can refer this as below:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I have used a measure approach

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785