Visualisation and Calculation problem

[EMGI]

Hello everyone,

I'm a Newbie to Power BI and looking for some help.

In an excel-table i have a lot of costumer informations. In each row in separate columns is a StartDate, EndDate and a Quantity. Different quantities generates a new line for a peroid.

For example:

ChargeStartDate | ChargeEndDate | Quantity

15.09.2017           30.09.2017         161

01.10.2017           09.10.2017         163

10.10.2017           14.10.2017         162

My problem is now to get the quantity for each day. I have no idea how to create a measure/column to get this work.

Finally i just want a matrix table in powerbi with the product in the rows, in the columns each days date and the quantity as the value.

I hope somebody has an idea. Thanks in adavance for your help.

Best wishes

EMGI

[Eric_Zhang]

---

@EMGI wrote:

Hello everyone,

I'm a Newbie to Power BI and looking for some help.

In an excel-table i have a lot of costumer informations. In each row in separate columns is a StartDate, EndDate and a Quantity. Different quantities generates a new line for a peroid.

For example:

ChargeStartDate | ChargeEndDate | Quantity

15.09.2017           30.09.2017         161

01.10.2017           09.10.2017         163

10.10.2017           14.10.2017         162

My problem is now to get the quantity for each day. I have no idea how to create a measure/column to get this work.

Finally i just want a matrix table in powerbi with the product in the rows, in the columns each days date and the quantity as the value.

I hope somebody has an idea. Thanks in adavance for your help.

Best wishes

EMGI

---

@EMGI

If you're expecting an output as below, you can reference the attached pbix file.

[EMGI]

Thanks for your replies.

I tried your solution, @Eric_Zhang, but i have two problems.

1. If the quantity changes, powerbi duplicates the first value for each date

2. The second problem is in the following picture:

The imported excel-file looks like that. The red surrounded lines i do not want in my report.

I hope you understand my problem better than before.

Thanks in advance...

[jthomson]

Power Query can handle this, I've got a custom column that looks like this:

= Table.AddColumn(#"previous step", "newcolumn", each List.Dates([Start Date], Duration.TotalDays([End Date]-[Start Date])+1, #duration(1, 0, 0, 0)))

You then expand that list and it'll give you an entry for each date between your start and end date which you can then use in your visual.

Also try looking at https://community.powerbi.com/t5/Desktop/Getting-all-dates-between-2-dates/td-p/60577 which has a similar issue/solution