Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
Hello Xiaotang, thanks for analysis of my problem. Your solution resolves my issue! Thank you for help. 🎉
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |