Get the last value of each month and year

[LamSar]

Good morning,

I have a question concerning a measure I created to calculate the overhead cost per month and year.

The 1st measure: 

Overhead= 

VAR valuelastdateoh = CALCULATE(

AVERAGE(Artikelcalculatie[Overhead per BHE]),

FILTER(Artikelcalculatie, Artikelcalculatie[Geldig van] = MAX(Artikelcalculatie[Geldig van])))

VAR Averageofvalueslastdateoh =

AVERAGEX(VALUES(Datum[MaandNummer]), valuelastdateoh)

RETURN

Averageofvalueslastdateoh

The 2nd measure:

Overhead (EUR) =

Var Overhead = [Overhead]

Var VerkoopKG = [Verkoop (KG)]

RETURN

Overhead * VerkoopKG

The goal is to retrieve the last value per month. And on year bases calculate the average.

For year 2020 and 2022 I obtain the correct results. 

For year 2021 the Overhead is zero.... 

The dataset holds the data for 2021.

What am I doing wrong?

[amitchandak · Accepted solution]

@LamSar , For last value for month you can try measure using time intelligence

MTD Sales = CALCULATE(lastnonblankvalues('Date'[Date],SUM(Sales[Sales Amount])),DATESMTD('Date'[Date]))

or use date time from table

MTD Sales = CALCULATE(lastnonblankvalues('Sales'[Date time],SUM(Sales[Sales Amount])),DATESMTD('Date'[Date]))

Other measure

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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.