How to Return Latest Value in Column Based on Date

[VictorV]

Hi everyone,

I need help on this one, I want to create a measure that show lastest Amount based on month. For example, If I select month February, it will show lastest amount of January is 3000.

Please, help me!!!

[VijayP]

@VictorV   Try this file attached

[VictorV]

Tks for your replied, but I don't want it SUM, I want it appear like the last value of that month, you can see in my pic, the last day is 1/30/2024 with 3000, so I want it appear amount 3000 like that

For example in your pi file, if you select month feb, it should be appear 422 of 1/21, cause this is the last day in that month 

[Ashish_Mathur]

Hi,

Ceate a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number column.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  Create a slicer and drag Year and Month name from the Calendar Table.  Select April 2024.  Write these measures

Total = sum(Data[Amount])

Balance = calculate([Total],lastnonblank(calendar[date],calculate([Total])))

Hope this helps.

[VictorV]

Hi, tks for your replied, It close to what I want, I want it like if I select March, it will appear last value of Feb, in this case is 389, but your measure is the lastest value in that month instead

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

Balance = calculate([Total],calculatetable(lastnonblank(calendar[date],calculate([Total])),datesbetween(Calendar[date],minx(all(calendar[date]),calendar[date]),min(calendar[date])-1)))

[VictorV]

Thanks a lot, it's work perfect

[Ashish_Mathur]

You are welcome.