Forum Discussion

cgil's avatar
cgil
Advocate I
4 years ago
Solved

Last value in a month

Hi all!

 

I need some help.

I have 3 columns in a summarize table (Provincia, Mes(month of the date of the main table, Total).

For each Mes(it is the month of a Date) I have sevaral values for the column "Total" and I only need the value according to the last date of each month.

 

For example:

 

For Tarragona I have 217 for December and 222, in this case according to the date I only need the 217 in mi table.

 

Can anybody help me?

thank you so much!

last value
  • cgil's avatar
    cgil
    4 years ago

    Hi all! In the end I solved it using Power M Query, I created a table with this 3 columns I need, then I sort them by the date and I add an index. 

    After that I used this formula in DAX: 

    if(COUNT(Consulta1[Fecha]) = 1, SUM(Consulta1[Total]), CALCULATE(SUM(Consulta1[Total]), FILTER(Consulta1, Consulta1[Índice] = max(Consulta1[Índice] ))))
     
    THanks for your help!

5 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago

    new measure = VAR _MaxDate=CALCULATE(MAX([date]),ALLSELECTED([Mes])) RETURN IF(MAX([date])=_MaxDate,SUM([Value]))

    • cgil's avatar
      cgil
      Advocate I
      4 years ago

      Hi! Thank you for your reply but it doesnt fit what I want...

       

      This is my table: 

      Resumen = SUMMARIZE(llamadas, LLAMADAS[Provincia],LLAMADAS[Fecha].[Mes],LLAMADAS[Total])
      I only look at month because I have values for each week and I only want the different ones.
       
      So I get the table of the question where you can see sevaral values for Tarragona in december.
      After applying your measure:

       

      It shows the same... 😥

       

      Any suggestion?

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Community Champion
        4 years ago

        Resumen = ADDCOLUMNS(SUMMARIZE(llamadas, LLAMADAS[Provincia],LLAMADAS[Fecha].[Mes],"Total",LASTNONBLANKVALUE(LLAMADAS[Fecha].[Date],SUM(LLAMADAS[Total])))

        you may translate .[Date] into your language.

  • cgil's avatar
    cgil
    Advocate I
    4 years ago

    Hi all! In the end I solved it using Power M Query, I created a table with this 3 columns I need, then I sort them by the date and I add an index. 

    After that I used this formula in DAX: 

    if(COUNT(Consulta1[Fecha]) = 1, SUM(Consulta1[Total]), CALCULATE(SUM(Consulta1[Total]), FILTER(Consulta1, Consulta1[Índice] = max(Consulta1[Índice] ))))
     
    THanks for your help!