Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Extract last data from a month

Hello everyone, I have this table and i just want to extract the last inventory of each month, i've been trying without succesfull results. I'd really appreciate some help:   The expected re...
az38's avatar
az38
Community Champion
6 years ago

So, Anonymous the solution with summarized table doesnt satisfy you?

try a measure like

Measure = calculate(lastnonblank('total base'[Inventario],1). allexcept('total base','total base'[Fecha]))

then in visual leave onle month-member of 'total base'[Fecha] hierarchy

do not hesitate to give a kudo to useful posts and mark solutions as solution

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Still not working

 

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    add .[Month]

    Measure = 
calculate(lastnonblank('total base'[Inventario],1), allexcept('total base','total base'[Fecha].[Month]))

    looks like this

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, az38  and thank you for your time and patience.

       

      Your last solution should have worked, but it has not. I don't know if there is a problem with the way that power bi sum the inventories in each week.

       
      inv final mes = calculate(lastnonblank('total base'[Inventario],1), allexcept('total base','total base'[Fecha].[Month]))

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Hi Anonymous 

        at last, i got it 🙂

        1. create a calculated column (not measure) in 'total base' table

        r = 
RANKX( filter('total base';'total base'[Fecha].[Month]=earlier('total base'[Fecha].[Month]) && not(isblank('total base'[Inventario])));'total base'[Fecha];;DESC)

        2. create a measure in 'total base' table

        inv final mes = 
calculate(lastnonblank('total base'[Inventario];sum('total base'[Inventario])); allexcept('total base';'total base'[Fecha].[Month]);'total base'[r]=1)

        next, leave only Month in Fecha hierarchy inside your visual

        do not hesitate to give a kudo to useful posts and mark solutions as solution