Forum Discussion

Catherine84's avatar
Catherine84
Icon for Helper I rankHelper I
8 years ago
Solved

Rolling data for 4 quarters

Hi,   Hoping the experts can help me with the dax below.   I have a set of data with quarter but no dates. I need to get a rolling 4 quarters ( 12 months) data for Sales and COGS. Ie for Q1 2017...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    You may download my PBI file from here.

     

    Hope this helps.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    Remove Account code from the column labels.  Try this measure

     

    Rolling 4 quarter sales value = CALCULATE([Sales QTD amount],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))

     

    Rolling 4 quarter COGS value = CALCULATE([COGS QTD amount],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    In the filter section (right hand side pane), click on Year and select Do not Summarize.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    There is no mistake in my formula.  I think there is a problem in the Date column of your Data Table.  The 4th quarter of 2016 should be March - May of 2017, so the date should be 1 March 2017 (not 1 March 2016 - as is appearing in your PBI file).  Please check.

     

    That is all i can help with.

Catherine84's avatar
Catherine84
Icon for Helper I rankHelper I
8 years ago

Thanks Ashish! :)

 

For this formula, we used a column data named "Data[QTD amount]". If my calculated data column is a measure instead, how should i modify the formula? Ie like i didnt have a sales/ COGS column but instead i have created a measure call Sales QTD amount using the QTD amount filtered with a category. I cant seems to find the measure to replace the Data[QTD amount] below in the formula.

 

Rolling 4 quarter value = CALCULATE(SUM(Data[QTD amount]),DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

Remove Account code from the column labels.  Try this measure

 

Rolling 4 quarter sales value = CALCULATE([Sales QTD amount],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))

 

Rolling 4 quarter COGS value = CALCULATE([COGS QTD amount],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))

  • Catherine84's avatar
    Catherine84
    Icon for Helper I rankHelper I
    8 years ago

    Thanks for your help Ashish. As my dates are not in calendar format, ie Q1: Jun-Aug, Q2: Sep-Nov, Q3: Dec-Feb and Q4: Mar-May.

     

    I manage to build the calendar from the sample that you have shown basis the dates that i need to correspond to the above but when i pull out the rolling data using the formula, its doesnt work. Not sure if this only work on calendar basis?

     

    Rolling 4 quarter value = CALCULATE([Sales QTD Amt],DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-9),MAX('Calendar'[Date])))

     

    Thanks!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      You are welcome.  The formula should work ireespective of the months that fall in the year.  I will need to see yoru file with your formula.  I will make changes there.