Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a table with Pallets ID, quantity and date. These dates are production date, delivery date or modification date of Pallets ID.
I add an Index to show events in chronological order. I create another table "Date" to select a date for the creation of the report.
By choosing a date from table Date with a slicer, I want to see the quantity of each Pallets ID and make a sum of it.
e.g. for Pallets F19220836; if the date in the slicer is november 15, quantity woulb be 1778. If date is november 10, quantity would be 0 and if date is november 22, it would be 0 too.
Considered that there can be many rows (for many modification) for each Pallets ID.
How can I acheived this?
Thanks
Hi @FRG ,
We can create a date table as below and make it related to the fact table.
date = CALENDARAUTO()
Then we can get the excepted result by slicers.
For more details, please check the pbix as attached.
Thanks for the reply.
If the date in the slicer in novembre 11, I need to see No_Palet F003 with quantity of 30. It's always the last know value for the date slicer.
Hi @FRG ,
We can insert an index column in power query. Before that we should sort the table by date column. M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjMwMFTSUTI0ABGGCn75Zam5SalFCkYGhpZKsTpwBUYgBUY4FBjBFOAywZiQCSAFxmATFBRw22GIzwhDJCPQFMQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No_PALET = _t, Quantity = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No_PALET", type text}, {"Quantity", Int64.Type}, {"Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1)
in
#"Added Index"
Then we can create a measure to get the excepted result.
Measure =
VAR index =
MIN ( Table1[Index] ) - 1
RETURN
CALCULATE (
SUM ( Table1[Quantity] ),
FILTER ( ALL ( Table1 ), Table1[Index] = index )
)
For more details, please check the pbix as attached.
Hi @v-frfei-msft ,
Thanks for your quick response. I miss explained the expecting result. I want to sum the quantity of all No_Palet.
For novembre 11:
F001 = 10
F002 = 20
F003 = 30
Total = 60
Thaks for your help!
Hi @FRG ,
Delete the relationship between date table and the fact table and update the measure as below.
Measure =
VAR da =
MAX ( 'date'[Date] )
VAR nop =
CALCULATETABLE (
VALUES ( Table1[No_PALET] ),
FILTER ( Table1, 'Table1'[Date] = da )
)
VAR asum =
CALCULATE ( SUM ( Table1[Quantity] ), FILTER ( Table1, 'Table1'[Date] = da ) )
VAR a =
CALCULATETABLE (
DISTINCT ( Table1 ),
FILTER ( Table1, NOT ( 'Table1'[No_PALET] IN nop ) && 'Table1'[Date] < da )
)
VAR notinsum =
CALCULATE ( SUM ( Table1[Quantity] ), KEEPFILTERS ( a ) )
RETURN
notinsum + asum
Pbix as attached.
Hi,
We are very close of what I need. In the first post I said that I created a Index column. The reason is because sometimes, in the same day, it can happen many operation on a No_Palet. So I need to use only the last operation of the day.
Can we use this index to acheived that?
Thanks for your precious help!
User | Count |
---|---|
98 | |
89 | |
82 | |
71 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |