Report by date

[FRG]

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

[v-frfei-msft]

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.

[FRG]

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.

[v-frfei-msft]

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.

[FRG]

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!

[v-frfei-msft]

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.

[FRG]

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!