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

     

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