Find changes in SCD tables in selected time period

[Anonymous]

Hello,

Been struggeling with this for a while and im confident there are alot of people here who can give good input.

Lets say I have order data as in below table using SCD. In this example Order2's delivery date has been set to 1st of August on 1st of June, but on 1st of August it was changed to 1st of September. 

validto = NULL means that the post is currently valid

OrderID	Order	OrderValue	Orderdate	Validfrom	validto
1	Order1	100	2018-08-01	2018-06-01	NULL
2	Order2	200	2018-08-01	2018-06-01	2018-08-01
2	Order2	200	2018-09-01	2018-08-01	NULL
3	Order3	300	2018-08-01	2018-06-01	NULL

I need to find, for any given order and time period (e.g. a specific month), which order has been pushed to a period after the selected period.

Desired output is going to look something like this below.
When selecting month August, the measure "Pushed" will show all the orders which had an original date in the current month but that has been pushed outside (after) the current period.

Order	Order value	Pushed
Order1	100	0
Order2	0	200
Order3	300	0

I've built this in SQL using CTE and cross joins with fixed periods. Now I have been searching for a way to implement this in Power BI to utilize the more dynamic approach of dates and hierarchies.

Any useful links or tips is appreciated. I have some good general knowledge about DAX, PQ and date handling in PBI but haven't been able to find a good example for this kind of topic yet.

[v-yuezhe-msft]

@Anonymous,

What logic do you use to get the order value(300) for Order3? When selecting Month August, the OrderDate(2018-10-1) for Order3 is greater than August.

Could you please share the CTE query here?

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft 

Valid point! That was a typo, the orderdate for order3 should also be August and I have now edited the table.

Regarding the CTE it's a bit complcated to show since I then need to rewrite it to remove business logic.
It is also not really comparable with what I want to do in Power BI since the SQL was a quick and dirty way to get a first draft of the data needed and I am not sure that I even need to use Joins in PBI to get the same result.

Anyway, what I did was to create a temp date table containing all the periods (e.g. year and month) for the current orders.
After that I used loops and joins to join the data from the CTE joins (orders in this example) with the date table.

CTE join is basically similar to this:

CTE1.orderid = CTE2.orderid
AND CTE1.validto = CTE2.validfrom
AND CTE1.orderdate <> CTE2.orderdate

WHERE 
CTE2.orderdate > lastdayofcurrentperiod
AND CTE1.orderdate < lastdayofcurrentperiod
AND CTE1.orderdate > firstdayofcurrentperiod

Which for a given year - month i get the old and new values for, in this case, the orderdate and ordervalue.
 

[v-yuezhe-msft]

@Anonymous,

Based on the above sample data, you can create the following columns in your table.

YearMONTH = FORMAT(Table[Orderdate],"YYYY-MMM")

maxorderdate = CALCULATE(MAX(Table[Orderdate]),ALLEXCEPT(Table,Table[Order]))

maxvaildfrom = CALCULATE(MAX(Table[Validfrom]),ALLEXCEPT(Table,Table[Order]))

Then create the following measures. For more details, please check attached PBIX file.

Order value = IF(FORMAT(MAX(Table[maxorderdate]),"YYYY-MMM")=MAX(Table[YearMONTH]),MAX(Table[OrderValue]),0)

Pushed = IF(FORMAT(MAX(Table[maxvaildfrom]),"YYYY-MMM")=MAX(Table[YearMONTH]),MAX(Table[OrderValue]),0)

Regards,

Lydia

[Anonymous]

Thank you for your response @v-yuezhe-msft

I've modified some of your solution and I believe I am almost there.
However, there are some things than needs to be considered.

First, there can be a situation where a an order has been changed back to it's original date, like this on Order2.

OrderID	Order	OrderValue	Orderdate	Validfrom	validto
1	Order1	100	2018-08-01	2018-06-01
2	Order2	200	2018-08-01	2018-06-01	2018-07-01
2	Order2	200	2018-09-01	2018-07-01	2018-08-01
2	Order2	200	2018-08-01	2018-08-01
3	Order3	300	2018-08-01	2018-06-01	2018-07-01
3	Order3	301	2018-08-01	2018-07-01

This will cuase some issues, first with the maxorderdate column, however this is probably fixed by rewriting it to show the current valid order date instead, adding a filter on validto = blank().
The "pushed" measure will however in this scenario show the order value even if it's actually set for delivery in the current month. 

The validfrom date can also have completely other dates. If validfrom on the last row of order2 in the first example would have been, lets say, 1st of july, the "pushed" measure would have showed 0, which it shouldn't.

Do you have any tips on how to fix this? I'm also trying to figure it out and will post here if i get successful.

Thanks again. 

[v-yuezhe-msft]

@Anonymous,

Please post expected result based on the new sample data you have post.

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft,

Here's some new values with some more variety in dates.

OrderID	Order	OrderValue	Orderdate	Validfrom	validto
1	Order1	100	2018-08-01	2018-06-01
2	Order2	200	2018-08-01	2018-06-01	2018-07-01
2	Order2	200	2018-09-01	2018-07-01	2018-07-20
2	Order2	200	2018-08-01	2018-07-20
3	Order3	300	2018-08-01	2018-06-01	2018-07-01
3	Order3	301	2018-11-20	2018-07-01
4	Order4	400	2018-08-01	2018-05-20	2018-06-16
4	Order4	400	2018-10-01	2018-06-16

With this data I am looking for a result like this below, when selecting month August:

Order	Value	Pushed
Order1	100	0
Order2	200	0
Order3	0	301
Order4	0	400

Order 1 & 2 have current valid orderdates in August and Order 3 & 4 had valid orderdates in August but have been pushed forward to November and October.

Br,
Max

[v-yuezhe-msft]

@Anonymous,

Create the following measures in your table. For more details, please check attached PBIX file.

countorder per selection = COUNT(Sheet6[Order])

Order value = IF(FORMAT(MAX(Sheet6[maxorderdate]),"YYYY-MMM")=MAX(Sheet6[YearMONTH])||[countorder per selection]>=2,MAX(Sheet6[OrderValue]),0)

Pushed = IF(MONTH(MAX(Sheet6[maxorderdate]))>=MONTH(MAX(Sheet6[YearMONTH]))&&MONTH(MAX(Sheet6[maxvaildfrom]))<=MONTH(MAX(Sheet6[YearMONTH]))&&[Order value]=0,MAX(Sheet6[Column]),0)

Regards,
Lydia

[Anonymous]

Thank you @v-yuezhe-msft,

I've been playing around with this and trying to modify it to match my needs, but I am not quite there yet.

One thing is the issue with the total values in table/matrix. Currently this solution is not working good since it's using the MAX() value and not summarizing the values as a total..

I've been trying to use SUMX() but am running into problems with SCD data since i have multiple rows for each order, which results in duplicated values. Do you know any good workarounds for this?

Regards,

Max

[v-yuezhe-msft]

@Anonymous,

Kindly share me your PBIX file.

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft This is from the same pbix that you shared earlier, "find-value-mod". As you can see the total value is showing only the MAX() value on the "Order value" column and 0 on the "Pushed" column. 

What i need is measures to show the correct total value aggregated from each row. In this case 300 as "Order value" and 701 as "Pushed"

[Anonymous]

I'll try and be a bit more specific.

Some more detailed data:

OrderID	Order	OrderValue	ActualOrderDate	EstimatedOrderDate	Validfrom	validto
1	Order1	100		2018-08-01	2018-06-01	2018-07-13
1	Order1	100		2018-11-20	2018-07-13
2	Order2	200		2018-08-01	2018-06-01	2018-07-01
2	Order2	200		2018-09-01	2018-07-01	2018-07-20
2	Order2	200		2018-08-01	2018-07-20	2018-08-20
2	Order2	200	2018-08-20	2018-08-20	2018-08-20
3	Order3	300		2018-08-01	2018-06-01	2018-07-01
3	Order3	301		2018-11-20	2018-07-01
4	Order4	400		2018-08-01	2018-05-20	2018-06-16
4	Order4	400		2018-10-01	2018-06-16	2018-10-01
4	Order4	400	2018-10-01	2018-10-01	2018-10-01

I also have a separate date table with two relationships. The active one is for the actual order date and the inactive one for the estimated order date. 

In the report I need to select e.g. August(or any other period) and get all the orders and their order values (based on those who have actual order date that month) and a second measure (column) showing all the orders and values who has had estimated order value this month, but have been pushed forward (after the selected month). Like this:

I managed to get "Order value" measure working for this, however the "Pushed" measure is only returning the MAX() value for each row I cant get the totals to work. Sometimes, as in this example its showing 0 instead. And sometimes when I've been experimenting its only returning the MAX() value of all the rows (301 in this example).

I've been trying to get SUMX to work but since i have multiple rows for each order and inactive relationship to consider, i just cant get it to work without getting back duplicated values (because of the multiple rows). Been trying both VALUES(), SUMMARIZE() and DISTINCT() but cant get a grip on it.

There are also some calculated columns included in this.

Current measures:

Order value = 
SUMX(VALUES(Sheet6[OrderID]);
 CALCULATE(
 IF(AND(MAX(Sheet6[currentestimatedorderdate])>=MIN('Date'[Date]);
 MAX(Sheet6[currentestimatedorderdate])<=MAX('Date'[Date]))||[countorder per selection]>=2;MAX(Sheet6[OrderValue]);0)))

Not sure about the [countorder per selection] measure, since in reality there can be two or more rows without the dates beeing changed at all. That will probably break this. 

Pushed = CALCULATE(IF(MAX(Sheet6[currentestimatedorderdate])>=MAX('Date'[Date])&&
MAX(Sheet6[maxvaildfrom])<=MAX('Date'[Date])&&
[Order value] = 0; MAX(Sheet6[Column]);0);
USERELATIONSHIP('Date'[Date];Sheet6[EstimatedOrderDate]))

Currently anything will help. Small tips, articles, blog posts, working examples...
Basically I need an iterator to sum the values without returning all the rows.

Thanks in advance.
Br

Max

[v-yuezhe-msft]

@Anonymous,

Please share your PBIX file here. At the beginning, your expected result is as below. However, from your recent reply, you have changed the original table structure and desire to get a different result based on slicer selection. Could you please post your new final result ?

Order	Value	Pushed
Order1	100	0
Order2	200	0
Order3	0	301
Order4	0	400

Regards,
Lydia