Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cgil
Advocate I
Advocate I

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:

cgil_0-1640847856111.png

 

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!

1 ACCEPTED SOLUTION
cgil
Advocate I
Advocate I

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!

View solution in original post

5 REPLIES 5
cgil
Advocate I
Advocate I

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!
smpa01
Super User
Super User

@cgil  can you provide some sample data?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
wdx223_Daniel
Super User
Super User

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

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:
cgil_0-1640849943931.png

 

It shows the same... 😥

 

Any suggestion?

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

you may translate .[Date] into your language.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors