Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DYNAMIC QUARTER MOVIL

Hi everybody

 

I want to create a measure that give me QUARTERL SALES, for example:

 

SALES1 = JAN, FEB, MAR      (IN MARCH)

SALES2 = FEB, MAR, APR      (IN APRIL)

SALES3 = MAR, APR, MAY      (IN MAY)

SALES4 = APR, MAY, JUN      (IN JUNE)

SALES 5 = MAY, JUN, JUL      (IN JULY)

.

.

.

 

I tried this:

 

VAR Date1= Max(Calendar[Date])

VAR Date2 = Date1 - 1

VAR Date3 = Date1 - 2

RETURN CALCULATE([Sales];Calendar[Date]=Date1) + CALCULATE([Sales];Calendar[Date]=Date2) + CALCULATE([Sales];Calendar[Date]=Date3)

 

But I did not make it. I was learning using DAX

 

 

  • Hello Anonymous ,

     

    Try this:

     

    Qtr Sum2 = 
    
    VAR _StartMonth = 
    MAX(Calendar[Date])
    
    
    VAR _QtrSum = 
        CALCULATE(
            [Sales],
            DATESINPERIOD(Calendar[Date],_StartMonth,-3,MONTH)
        )
    
    RETURN
    
    _QtrSum

     

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution
    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    Connect on LinkedIn

2 Replies

  • vivran22's avatar
    vivran22
    Community Champion

    Hello Anonymous ,

     

    Try this:

     

    Qtr Sum2 = 
    
    VAR _StartMonth = 
    MAX(Calendar[Date])
    
    
    VAR _QtrSum = 
        CALCULATE(
            [Sales],
            DATESINPERIOD(Calendar[Date],_StartMonth,-3,MONTH)
        )
    
    RETURN
    
    _QtrSum

     

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution
    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    Connect on LinkedIn

    • Anonymous's avatar
      Anonymous
      Not applicable

      The measure works very well!! Eres un crack !!!

       

      Share the result:

       

      EQC Promotores 3MM =

      VAR _StarMonth = MAX('BD Calidad Venta'[FECHA])
      VAR _QtrSum = CALCULATE(
      [EQC Promotores Acum.];
      DATESINPERIOD('BD Calidad Venta'[FECHA];_StarMonth;-3;MONTH)
      )
      RETURN
      _QtrSum
       
      Thanksss !!