Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 - D...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello Xiaotang, thanks for analysis of my problem. Your solution resolves my issue! Thank you for help. ðŸŽ‰