last value for client

[mic_rys]

hi, i have a little issue, i need to choose always last value by client. examples:

Client	User	Date	Value
client1	us1	02.02.2020	10,4
client1	us1	02.02.2020	2
client1	us2	01.04.2020	3
client2	us3	01.08.2020	1,2
client2	us3	01.10.2020	6
client3	us4	01.11.2020	15,2

I have date tale too with, year,quarter, month, day

Could you help me to create a measure which shows always last value. Nice to work with date dimensions;

1. only client - value by client from last date

.2 client, year- value by client,year from last date

3. client, year, month - value by client,year,month from last date

i tried:

measure =
var datelast= CALCULATE(LASTNONBLANK([Date],TRUE()))

return
CALCULATE(sum(value),FILTER(date table, date = datelast))

but something is not good;/

thanks !

[amitchandak]

@mic_rys ,Output is not clear, Suggesting few measure. In case you need group year month , add that in date table

Create a calendar Table .

Date = calendar(min(Table[Date]), Max(Table[Date]) )

Join it date of you table

Try measure like

measure last date =
var datelast= CALCULATE(LASTNONBLANK(Table[Date],TRUE())) //or use maxx(Allselected(Table), Table[Date])

return
CALCULATE(sum(value),FILTER(Date, Date[Date] = datelast))

measure this year =
var datelast= CALCULATE(LASTNONBLANK(Table[Date],TRUE())) //or use maxx(Allselected(Table), Table[Date])
var _min = date(year(datelast),1,)
return
CALCULATE(sum(value),FILTER(Date, Date[Date] <= datelast && Date[Date] >= _min))

measure this month =
var datelast= CALCULATE(LASTNONBLANK(Table[Date],TRUE())) //or use maxx(Allselected(Table), Table[Date])
var _min = eomonth( datelast,-1)+1
return
CALCULATE(sum(value),FILTER(Date, Date[Date] <= datelast && Date[Date] >= _min))

Refer why TI fails, to check what end date of calendar will do - https://www.youtube.com/watch?v=OBf0rjpp5Hw

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.