Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KamilSzpyrka
Helper III
Helper III

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:

KamilSzpyrka_1-1652798683204.png

 

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.

KamilSzpyrka_2-1652798764557.png

 

I will highly appreciate any help on this. 

Regards,

Kamil Szpyrka

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1653030784682.pngvxiaotang_1-1653030792646.pngvxiaotang_2-1653030800755.pngvxiaotang_3-1653030810559.png

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.

View solution in original post

2 REPLIES 2
v-xiaotang
Community Support
Community Support

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

vxiaotang_0-1653030784682.pngvxiaotang_1-1653030792646.pngvxiaotang_2-1653030800755.pngvxiaotang_3-1653030810559.png

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.