Hi all,
I'm trying to create an area chart wherein the amount of reuse of products is summed up.
CustomerName | ProductName | DateID
---------------- |--------------- |------------
CustomerA | ProductA | 20180101 (Same product linked to customer)
CustomerA | ProductA | 20180202 -------------------------------
CustomerB | ProductA | 20180203
CustomerC | ProductB | 20180214
CustomerA | ProductC | 20180305 (Same product but different version linked to customer)
CustomerA | ProductC 2.0 | 20180411 -----------------------------------------------------
In this example CustomerA has been linked to ProductA twice. I want to count these as 1 on the earliest entry date. ( Dates which aren't the minimum date will be left out )
For the last 2 records it's the same although the product version is different. This is an upgrade, not a reuse of product. The version of the product should be grouped with the main level of that product.
The area chart needs an axis with YearMonthNo (e.g. 201801, 201802)
This example will result in:
201801 : 1
201802 : 2
201803 : 1
201804 : 0
These values should be summed up each month, at YearMonthNo the value will be 4.
In powerBi im getting the correct results in a table but not in an area chart (for a table i can use 'min of dateID').
For this visualization im using the following tables:
- Fact.ProductCustomer
- Dim.Customer
- Dim.Product
- Dim.Date
The dimensions are linked to the fact by CustomerID, ProductId and DateID.
Thanks in advance,
Gio
