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. 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

 

Need Help

6 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    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.

     

    • 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.