Forum Discussion

cwnoll's avatar
cwnoll
Frequent Visitor
6 years ago

Help with lookback measure

I am trying to write a new measure that will go back 12 months, from date, and avg the next 6 months from that 12 month look back.

 

So the general idea is go back 12 months then avg -12 mths thru -6 mths from given month yr. I do have a date table, but the chart is using Month Year, instead of day.

 

month yrSalesLook-back
Jan 20171000 
Feb 20171001 
Mar 20172002 
Apr 20173003 
May 20171004 
Jun 20171005 
Jul 20171007 
Aug 20172008 
Sep 2017312 
Oct 20173134 
Nov 20173138 
Dec 20176285 
Jan 20187521 
Feb 20181546Avg Sales (Feb 2017 thru Aug 2017)
Mar 20182549Avg Sales (Mar 2017 thru Sep 2017)
Apr 201855674Avg Sales (Apr 2017 thru Oct 2017)
May 20183232AVG Sales (May 2017 thru Nov 2017)
Jun 20186464Avg Sales (Jun 2017 thru Dec 2017)
Need Help
Tutorial Requests

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    I think you are looking for 6 before 6. make sure you have date table.

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))  
  
Rolling last 6 before 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-6,MONTH)),-6,MONTH))

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi cwnoll ,

     

    We can try to use the following measure to meet your requirement:

     

    Look-back =
VAR d =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Sales'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Date]
                >= DATE ( YEAR ( d ) - 1, MONTH ( d ), 1 )
                && 'Calendar'[Date]
                    < DATE ( YEAR ( d ) - 1, MONTH ( d ) + 7, 1 )
        )
    ) / 6

     

    If it does not meet your requirement, could you please describle the fields of each tables and the relations between tables simply? Please don't have any Confidential Information or Real data in your reply.


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi cwnoll ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,