Forum Discussion
Stock Aging with SCD2
Hello,
I have a table representing stock aging in the Type 2 SCD (Slowly Changing Dimension). The data looks like below:
The columns represents:
FK_Product - Product Name
FK_Document - Document Number
Date of Document - when the product appeared in warehouse (from this date we will calculate aging)
Date From - SCD2 column - represents from which date given number of items left
Date To - SCD2 column - represents to which date given number of items left
Items Left - SCD2 column - represents how many items left in given range
Having this input I need to create a report, which will reflect how many items are left in given aging periods. User will choose a date on slicer for which the report will reflect data. 4 different samples below - values are presented with row(s) number (from previous screen) from which the data has been taken, so it can be easlier understood.
I will highly appreciate any help on this.
Regards,
Kamil Szpyrka
Hi Anonymous
You can try this, create the measures below
aging days = DATEDIFF(MIN('Table'[Date of Document]),SELECTEDVALUE(Slicer[date]),DAY)0-30 days = CALCULATE(SUM('Table'[Items Left]),FILTER(ALL('Table'),'Table'[FK_Product]=MIN('Table'[FK_Product]) && 'Table'[Date From]<=SELECTEDVALUE(Slicer[date]) && 'Table'[Date To]>= SELECTEDVALUE(Slicer[date]) && [aging days]>=0 && [aging days]<=30))31-60 days = CALCULATE(SUM('Table'[Items Left]),FILTER(ALL('Table'),'Table'[FK_Product]=MIN('Table'[FK_Product]) && 'Table'[Date From]<=SELECTEDVALUE(Slicer[date]) && 'Table'[Date To]>= SELECTEDVALUE(Slicer[date]) && [aging days]>=31 && [aging days]<=60))61-90 days = CALCULATE(SUM('Table'[Items Left]),FILTER(ALL('Table'),'Table'[FK_Product]=MIN('Table'[FK_Product]) && 'Table'[Date From]<=SELECTEDVALUE(Slicer[date]) && 'Table'[Date To]>= SELECTEDVALUE(Slicer[date]) && [aging days]>=61 && [aging days]<=90))result
For more please see the sample file attached below.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous
You can try this, create the measures below
aging days = DATEDIFF(MIN('Table'[Date of Document]),SELECTEDVALUE(Slicer[date]),DAY)0-30 days = CALCULATE(SUM('Table'[Items Left]),FILTER(ALL('Table'),'Table'[FK_Product]=MIN('Table'[FK_Product]) && 'Table'[Date From]<=SELECTEDVALUE(Slicer[date]) && 'Table'[Date To]>= SELECTEDVALUE(Slicer[date]) && [aging days]>=0 && [aging days]<=30))31-60 days = CALCULATE(SUM('Table'[Items Left]),FILTER(ALL('Table'),'Table'[FK_Product]=MIN('Table'[FK_Product]) && 'Table'[Date From]<=SELECTEDVALUE(Slicer[date]) && 'Table'[Date To]>= SELECTEDVALUE(Slicer[date]) && [aging days]>=31 && [aging days]<=60))61-90 days = CALCULATE(SUM('Table'[Items Left]),FILTER(ALL('Table'),'Table'[FK_Product]=MIN('Table'[FK_Product]) && 'Table'[Date From]<=SELECTEDVALUE(Slicer[date]) && 'Table'[Date To]>= SELECTEDVALUE(Slicer[date]) && [aging days]>=61 && [aging days]<=90))result
For more please see the sample file attached below.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.