Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  SUM of 3 Month Average

    Silver Contributor
    Posted 24 days ago
      |   view attached

    I'm working on a model that will flag items that may fall below expected demand. In order to do this, I need to calculate the average monthly usage for each month (January average, February average, etc.). Then create a measure that will SUM the monthly averages for the next 3 months. Please help me accomplish this in DAX.

     



    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------

    Attachment(s)



  • 2.  RE: SUM of 3 Month Average

    Posted 23 days ago
    Morning Nancy,

    In your calendar DIM create 2 columns. One with RANKX, will give you index to your months and then next column I call it MONTH RELATED as Rank in a row minus current month Rank. At the end you should land with column, when ALWAYS current month will be 0, last month, -1, previous -2 , etc. And following month will 1, 2 months ahead 2 and so on. Than you can write mesure to calculate average for all months when rank is less than 3 from month you are calculate average.

    I hope it will help.

    Regards,

    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 3.  RE: SUM of 3 Month Average

    Silver Contributor
    Posted 23 days ago
    Adding the offset to the Dates table will simplify the filter criteria, but I'm struggling to get a measure that will give me a SUM of the average.

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------



  • 4.  RE: SUM of 3 Month Average

    Posted 22 days ago
      |   view attached
    Olá
    Veja se o anexo ajuda

    ------------------------------
    Vilmar Santos
    ------------------------------

    Attachment(s)



  • 5.  RE: SUM of 3 Month Average

    Silver Contributor
    Posted 22 days ago
    The measure you created @Vilmar Santos does provide the sum of the average, but I need for it to always give me the sum of the next 3 months. So in November, the measure would find the overall averages of December, January, and February​​ and then add these three numbers together. Regardless of the filters on the page, the measure would always show the sum of the average for the next 3 months. This allows the manufacturing team to look at the quantity on hand and determine if we have enough on hand to make it through the next 3 months.

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------



  • 6.  RE: SUM of 3 Month Average

    Bronze Contributor
    Posted 21 days ago
      |   view attached
    Hi Nancy,

    Does this give you the desired result? For convenience, I added a new month column in the date table that also contains the year, then I changed the measure "Avg Usage" so that it is just a regular average connected to the date table trough delivery date. Finally, I added this measure:

    Sum of Average Usage Next Three Months = 
    VAR SelectedMonth =
        SELECTEDVALUE ( Dates[Month] )
    VAR LastMonthInPeriod =
        DATE ( YEAR ( SelectedMonth ), MONTH ( SelectedMonth ) + 3, 1 )
    VAR Result =
        CALCULATE (
            SUMX ( VALUES ( Dates[Month] ), [Avg Usage] ),
            REMOVEFILTERS ( Dates ),
            Dates[Month] > SelectedMonth,
            Dates[Month] <= LastMonthInPeriod
        )
    RETURN
        Result​
    Please note that the way it is currently defined, it only gives a result when a single month/year-combination is visible in the filter context.

    ------------------------------
    Tomas
    ------------------------------

    Attachment(s)