Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Report by date

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....
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-frfei-msft
Community Support
6 years ago

Hi Anonymous ,

 

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      v-frfei-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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!