Calculated Column to return latest value of another column by unique product, for each month

[Anonymous]

Hello Community, 

I would like to accomplish the same solution achieved in this article, but to modify the latest date constraint to apply for *each month*.

https://stackoverflow.com/questions/55474913/how-to-get-the-latest-values-of-a-column-per-each-disti...

Below is what I would like to achieve - End of Month Value is the desired column.

Thanks in advance for your input!

[Anonymous]

Apologies! My sample table was wrong - here is the correct table showing desired results:

[Whitewater100]

Hi:

Please have Date Table connected to this your Fact Table. Mark as Date Table. Form relationship with your Fact Table (Date to Stock Date).

First Measure:

Stock Value = SUM(Table[Stock Value])

Last Value = CALCULATE( LASTNONBLANKVALUE(DISTINCT(Dates[Date]), [Stock Value]))

or

Last Value = CALCULATE([Stock Value]), LASTNONBLANK(Dates[Date], [Stock Value]))

If you want calculated column:

Lst Stock Month Price =
VAR currentMonth =

MONTH( Data[Stock Date])

VAR currentYear =

YEAR( Data[Stock Date] )

VAR latestpriceMonth =

CALCULATE (

LASTNONBLANKVALUE(Dates[Date],MAX(Data[Stock Value])),

FILTER ( ALL(Data), MONTH( Data[Stock Date]) = currentMonth && YEAR( Data[Stock Date]) = currentYear), ALLEXCEPT(Data, Data[SKU]))

return

latestpriceMonth

I hope this helps!

[Anonymous · Accepted solution]

Thank you so much! 

I actually figured out how to do it using ALLEXCEPT as follows:

1. Created column YearMonth =

YearMonth = CONCATENATE(YEAR([Stock Date]),MONTH([Stock Date]))

2. Calucated Column Formula:

End of month Value =

VAR Latest_Date_In_Month =

CALCULATE(MAX([Stock Date]),ALLEXCEPT([YearMonth],[SKU]))

RETURN

IF([Del/finish]=Latest_Date_In_Month, [Stock Value],0)

It seems like it produces the desired result - do you see any reason why it wouldn't?

[Whitewater100]

Awesome. The measures look good. Please mark as solution or kudos if you can.  Thanks!