Forum Discussion

cosminc's avatar
cosminc
Icon for Post Partisan rankPost Partisan
7 years ago
Solved

same months comparison over the years - DAX

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
Need Help
  • ElenaN's avatar
    ElenaN
    7 years ago

    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

3 Replies

  • ElenaN's avatar
    ElenaN
    Icon for Resolver V rankResolver V
    7 years ago

    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's avatar
      cosminc
      Icon for Post Partisan rankPost Partisan
      7 years ago

      Hi and thanks for your help

       
      @
       
       
  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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