I would like to return the date ranges for which products were substituted but I'm having difficulty with the filters.
I have a "sub" flag in my Orders table that indicates if a product was substituted (the product ID is different than that in our item master file). I have a calendar table relating the date to the PO Date. I would like to have my pivot table show only POs for the date range I have selected, but show the entire duration of the substitutions for those POs.
Example. During March-May 2017 product ID 123 (widgets) were substituted for product ID 456 (sprockets). First substitution day was March 1, last substitution day was May 31, duration was 92 days. If my timeline is set anywhere from March 2017 to May 2017 I want to see the sub 123 with those date values because we issued POs in those months where the substitution was occurring. If, however, I want to look at POs from June 2017 onward then I don't want that substitution to appear since we weren't subbing in that time frame.
Just looking at the first substitution date, if I use
First Sub Date:=CALCULATE(FIRSTDATE(Orders[PO Date]),Orders[Sub Status] = "Sub")
Then the value that is returned matches the timeline filter. In other words if I have selected April 2017 in the timeline then the measure will return April 1 as the earliest date and April 30 as the last sub date with duration of 30.
If I use
First Sub Date:=CALCULATE(FIRSTDATE(Orders[PO Date]),ALL(Calendar[Date]),Orders[Sub Status] = "Sub")
and have selected April 2017 on the timeline then I get the correct start and end date for the sub, but it also shows me product substitutions that did not occur within the timeline. So if I select June 2017 on the timeline I am still seeing substitutions with product 123 for the March-May time frame.
So, how do I create a date filter to just give POs for the filtered timeline, but to give me the full substitution history for those POs? I'm tying myself in knots trying to figure out which filter takes precedence!
Thanks for your help.
I have a "sub" flag in my Orders table that indicates if a product was substituted (the product ID is different than that in our item master file). I have a calendar table relating the date to the PO Date. I would like to have my pivot table show only POs for the date range I have selected, but show the entire duration of the substitutions for those POs.
Example. During March-May 2017 product ID 123 (widgets) were substituted for product ID 456 (sprockets). First substitution day was March 1, last substitution day was May 31, duration was 92 days. If my timeline is set anywhere from March 2017 to May 2017 I want to see the sub 123 with those date values because we issued POs in those months where the substitution was occurring. If, however, I want to look at POs from June 2017 onward then I don't want that substitution to appear since we weren't subbing in that time frame.
Just looking at the first substitution date, if I use
First Sub Date:=CALCULATE(FIRSTDATE(Orders[PO Date]),Orders[Sub Status] = "Sub")
Then the value that is returned matches the timeline filter. In other words if I have selected April 2017 in the timeline then the measure will return April 1 as the earliest date and April 30 as the last sub date with duration of 30.
If I use
First Sub Date:=CALCULATE(FIRSTDATE(Orders[PO Date]),ALL(Calendar[Date]),Orders[Sub Status] = "Sub")
and have selected April 2017 on the timeline then I get the correct start and end date for the sub, but it also shows me product substitutions that did not occur within the timeline. So if I select June 2017 on the timeline I am still seeing substitutions with product 123 for the March-May time frame.
So, how do I create a date filter to just give POs for the filtered timeline, but to give me the full substitution history for those POs? I'm tying myself in knots trying to figure out which filter takes precedence!
Thanks for your help.
