same months comparison over the years - DAX

[cosminc]

Hi

i need to buid a graph with for last 3 years (2019 inclusive)

what expression do i need to obtain comparable data (same months) - if i have data on 2019 for jan & feb also 2017 and 2018 have to be only for jan&feb

i use a calendar (1 jan 2017 till 31 dec 2019) connected with the data base

let's take a simple example

salesman    sales  year  month

YTD = calculate(sum(Source[Sales]),Source[MonthNo]<=2)

but 2 need to be built as a max month from max year from data base(not max month for each salesman) 

thanks!

Cosmin

[v-lili6-msft]

hi, @cosminc 

You could try this measure:

Measure = VAR MaxYear = CALCULATE(MAX('Date'[Year]), ALLSELECTED('Date'))
VAR MaxMonth = CALCULATE(MAX('Date'[Month]), FILTER(ALLSELECTED('Date'),'Date'[Year] = MaxYear))
return
CALCULATE(SUM(Table1[Qty]), FILTER('Date', 'Date'[Month] <= MaxMonth&&'Date'[Year]>=MaxYear-2))

Result:

By the way, if you don't select any value in silcer, it will return all the data.

and for ElenaN formula if you use slicer only filter two years data, it will return blank value.

Best Regards,

Lin

[ElenaN · Accepted solution]

Hello,

Would this help?

Measure 4 = 
VAR MaxYear = CALCULATE(MAX(Table5[Year]), ALL(Table5))
VAR MaxMonth = CALCULATE(MAX(Table5[Month]), Table5[Year] = MaxYear)
RETURN
CALCULATE(SUM(Table5[Sales]), FILTER(Table5, Table5[Month] <= MaxMonth && Table5[Year] >= MaxYear - 2))

Regards,

ElenaN

[cosminc]

Hi and thanks for your help

@v-lili6-msft

for me is wright the solution from Elena because i make a slide with fixed graphs and after that i cut the relationship with data filters.

same need but for the same graph but with monthly level - 3 bars for each - last month, previous month and month -12

help me please Elena with this also

i'll put in a separate post

thanks again for your help, i was stuck with this

Cosmin