Forum Discussion

multt0's avatar
multt0
Frequent Visitor
3 years ago
Solved

Calculate MAT with Date filter

I made MAT measure like below

 

mat = CALCULATE(SUM('Fct_Sales_IMS'[Values_LC]),
               DATESINPERIOD( 'CALENDAR'[Date], MAX('CALENDAR'[Date]), -12, MONTH))

 

 

1) I want to another measure for last year like below

 

I tried like below,,

VAR year_ = SELECTEDVALUE('CALENDAR'[Year])
VAR quarter_ = SELECTEDVALUE('CALENDAR'[Quarter])

RETURN
MAT_y-1 = 
CALCULATE(
CALCULATE(SUM('Fct_Sales_IMS'[Values_LC]),
        DATESINPERIOD( 'CALENDAR'[Date], MAX('CALENDAR'[Date]), -12, MONTH)),
        FILTER('CALENDAR', YEAR([Date]) = year_  -1))

 

Thanks

 

Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    Hi   multt0 

    try to create the measure with this (the VAR part seems unnecessary):

    MAT_y-1 = 
CALCULATE(
    SUM('Fct_Sales_IMS'[Values_LC]),
    SAMEPERIODLASTYEAR(DATESINPERIOD('CALENDAR'[Date], MAX('CALENDAR'[Date]), -12, MONTH))
)

     

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    Hi   multt0 

    try to create the measure with this (the VAR part seems unnecessary):

    MAT_y-1 = 
CALCULATE(
    SUM('Fct_Sales_IMS'[Values_LC]),
    SAMEPERIODLASTYEAR(DATESINPERIOD('CALENDAR'[Date], MAX('CALENDAR'[Date]), -12, MONTH))
)

     

    • multt0's avatar
      multt0
      Frequent Visitor
      3 years ago

      It is working,
      i can't find prev quater funtion like "SAMEPERIODLASTYEAR"

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        there is no such function😂, try to use DATEADD. It is more flexible. Most Time Intelligence functions return a list of dates, so be brave to nest them.

    • Silvie's avatar
      Silvie
      Helper I
      2 years ago

      Hi, 

       

      I'm new at calculating rather complex formulas. I need to calculate the MAT based on a measure. 

      Table with data see below including what we expect the MAT result should look like. 

      DateOOP - ABOOP - with EcoscoreExpected MAT result
      01/02/202262006571,96153290854,87 
      01/03/202275885416,73203187723,93 
      01/04/202281008926,08246855401,59 
      01/05/202280554289,31242783685,49 
      01/06/202280889841,36244188668,79 
      01/07/202283651207,72246026973,7 
      01/08/202290373368,73258385872,05 
      01/09/202288898086,38252980248,4 
      01/10/2022100886308,19355064447,87 
      01/11/2022114580142,92368026113,14 
      01/12/2022134022067,66446482406 
      01/01/2023120197150,76348696194,3733,06% = sum of ' OOP - AB' from 02/2022 till 01/2023  divided by the sum of 'OOP - with Ecoscore' from 02/2022 till 01/2023
      01/02/2023116118961,65335621916,2632.89% =  sum of ' OOP - AB' from 03/2022 till 02/ 2023 divided by the sum of 'OOP - with Ecoscore' from 03/2022 till 02/ 2023
      01/03/2023138040772,3402175919,5932.80%
      01/04/2023138203247,47386740063,9133.09%
      01/05/2023121148045,18302451488,7533.62%
      01/06/2023146359040,74380171755,0134,11%
      01/07/2023136880971,51356216795,6934.48%
      01/08/2023140059604,92358827668,5634,83%
      01/09/2023141468248,64364874261,4835,14%
      01/10/2023142689405,06366071051,7736.00%
      01/11/2023138292652,57358040963,3236.62%
      01/12/2023159139285,21440116959,0837.24%

      I used the same logic as mentioned in this solution, using 'SAMEPERIODLASTYEAR' but I'm getting other results. 

       

      This is the formula I used to calculate the MAT: 

      % Ecoscore MAT Y-1 = calculate(
      Ecoscore[% Ecoscore met A+B],
      SAMEPERIODLASTYEAR(DATESINPERIOD(Ecoscore[Date],max(Ecoscore[Date]),-12,month))
      )

      The formula for the % Ecoscore met A+B measure looks like this (and is correct) 

      =Calculate(

      Sum(Ecoscore[OOP]),

      Filter('Ecoscore','Ecoscore'[Ecoscore] In {"A","B"}))/

      Calculate(Sum('Ecoscore'[OOP]),Filter('Ecoscore','Ecoscore'[Ecoscore] In {"A","B","C","D","E"})

      )

      Could you help me out please? What am I doing wrong?

      Kind regards,

      Silvie