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

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!