Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Last known value

Hi, I have a table with many entries for the same NO_Palette. Each row is an operation on a NO_Palette (inventorying, selling). The row with the smaller index is the last known value. In these data sample I filtered for one No_Palette.

I need to know the last value of Cumulatif column on a specific date. So if I make a search for january 25th I will get 1283,16. But if I look for january 29th, I will get 0.

Thanks to all.

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous

    It seems that your sample file was uploaded by mistake.

    and If you want that when select date january 24th, it still display NO_Palette P19179332 information?

    If so, you need to keep the date table has no relationship with data table, otherwise, in january 24th, there should be not data.

    and you could try the logic of this formiula:

    Measure = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Table1'[Date_REP] ),
        FILTER ( Table1, Table1[Date_REP] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    CALCULATE (
        MAX ( Table1[Cumulatif] ),
        FILTER (
            Table1,
            Table1[Date_REP] =maxdate
                && Table1[Index]
                    = CALCULATE (
                        MIN ( Table1[Index] ),
                        FILTER ( Table1, Table1[Date_REP] = maxdate )
                    )
        )
    )
        + 0

    and here is my sample pbix file, please try it.

     

    Best Regards,

    Lin

4 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous

    You may try to create a measure like this 

    Measure = var maxdate=CALCULATE(MAX(Table1[Date]),FILTER(ALL(Table1[Date]),Table1[Date]<=MAX(Table1[Date]))) return
CALCULATE(MAX(Table1[Cumulatif]),FILTER(ALL(Table1[Date],Table1[Index]),Table1[Date]<=MAX(Table1[Date])&&Table1[Index]=CALCULATE(MIN(Table1[Index]),FILTER(Table1,Table1[Date]=maxdate))))

    Result:

    Basic data

    filer date is 1/12/2019

     

    Here is pbix file, please try it.

     

     

    If it is not your case, please share a simple pbix file and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

     

     

    Best Regards,

    Lin

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Lin.

      I want to know the last know value of  [Cumulatif] on a given date (a slicer). For NO_Palette P19179332, on january 24th and before, it did not exist yet. Between january 25th and january 27th I expect to have 1087,04 for result. And for january 28th and after, 0. For eventually make sum of Cumulatif of all NO_Palette and follow up the inventory for each week, month, and so one.

       

      Here there is sample data.

       

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        7 years ago

        hi, Anonymous

        It seems that your sample file was uploaded by mistake.

        and If you want that when select date january 24th, it still display NO_Palette P19179332 information?

        If so, you need to keep the date table has no relationship with data table, otherwise, in january 24th, there should be not data.

        and you could try the logic of this formiula:

        Measure = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Table1'[Date_REP] ),
        FILTER ( Table1, Table1[Date_REP] <= MAX ( 'Date'[Date] ) )
    )
RETURN
    CALCULATE (
        MAX ( Table1[Cumulatif] ),
        FILTER (
            Table1,
            Table1[Date_REP] =maxdate
                && Table1[Index]
                    = CALCULATE (
                        MIN ( Table1[Index] ),
                        FILTER ( Table1, Table1[Date_REP] = maxdate )
                    )
        )
    )
        + 0

        and here is my sample pbix file, please try it.

         

        Best Regards,

        Lin