Calculate sum according to last date in Power BI

[Analitika]

Hi,

I would like to ask how calculate sum which depends on last date. date=last column date?

[NikhilChenna]

Hi @Analitika ,

I think you can acheive this by using the below,

Measure a = 
var lastdate = MAX('table1'[date])

RETURN

CALCULATE(
SUM('table1'[abc],
FILTER('table1','table1'[date]= lastdate)
)

Try this change the tables and columns as per your table names. this will work.

Regards, 
Nikhil Chenna 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Analitika]

Yeah, I tried this but I am getting 0 instead of 1. So it is wrong.

[NikhilChenna]

Hi @Analitika ,

Try to return only lastdate first, and check if you are getting the lastdate and then return the Calculate part. 

Regards, 
Nikhil Chenna 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Analitika]

Seems last date ok, but still it summing all values in column so it is wrong.

[NikhilChenna]

Hi @Analitika ,

Sorry i got it know you can use the below to acheive your solution,

1. for eg. Create a measure for getting the sum of a column as below

total = 

SUM('table1'[abc])

2. Create a another measure for getting the sum for last date, 

Sumforlastdate = 
CALCULATE(
SUM('table1'[abc]),
LASTNONBLANK( 'Date'[date], total )
)

This will work, i have checked it.

Regards, 
Nikhil Chenna 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Analitika]

I have tried but still getting 0.

[pratyashasamal]

Hi @Analitika ,
Please try this calculation :-
Last Date Amount =
CALCULATE (
SUM ( Table[Amount] ),
TOPN ( 1, VALUES ( Table[Date] ), Table[Date], DESC )
)
OR 

 CALCULATE( SUM(Table[Amount]) , 'Date'[Date] = Table[LastUpdateDate] )

Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Analitika]

I am getting 0 with formula: Last Date Amount =
CALCULATE (
SUM ( Table[Amount] ),
TOPN ( 1, VALUES ( Table[Date] ), Table[Date], DESC )
)

[pratyashasamal]

Hi @Analitika ,
Please try this calculation as well .
 CALCULATE( SUM(Table[Amount]) , 'Date'[Date] = MAX( 'Date'[Date]))

Thanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Analitika]

nothing changed

[pratyashasamal]

Hi @Analitika ,
Can you please share a sample dataset to try on .
Thanks ,
Pratyasha Samal

[pratyashasamal]

Hi @Analitika ,
You can also try this 

Measure 2 = CALCULATE (

SUM ( Orders[Sales] ),

FILTER(Orders , Orders[Order Date] = Orders[Max date])

)

Max date = CALCULATE( MAX( Orders[Order Date]) , ALL(Orders))

Thanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Analitika]

not working