Stock Aging with SCD2

[KamilSzpyrka]

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

[v-xiaotang · Accepted solution]

Hi @KamilSzpyrka 

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.

[KamilSzpyrka]

Hello Xiaotang, thanks for analysis of my problem. Your solution resolves my issue! Thank you for help. 🎉