CODEWITHSUNDEEP
powerbidax
Ricardo J. Martínez Suástegui
Ricardo J. Martínez Suástegui

Reputation: 157

Rolling Year: Actual And Last Year

I have the following fields:

  1. Year
  2. Category
  3. Maker
  4. Month
  5. Month Number
  6. Sales Volume
  7. Sales
  8. Date

enter image description here

So, I have in my dash a filter for "Month Number" and "Year":

enter image description here

My goal is to create two new measure; first with the Rolling Year that need to sum 12 months, ending in the moment that the user select in the mencioned filters. For example if y select Year 2021 and Month 01. The Rolling Year need to sum the sales of a selected category since 2020-02 to 2021-01 (thats mean always 12 months since a pivot month).

For thesecond is exactly the same, a measure called Rolling Last Year, it need to be a rolling sum too, but for the last period in order to compare. Taking the same example if I have the period 2020-02 to 2021-01. The Rolling Last Year for the last period should be 2019-02 to 2020-01.

I tried with this DAX code, that extracted from Microsoft page but without success:

Rolling Year =
CALCULATE (
SUMX ( Table, Table[Sales] ),
FILTER (
ALL (Table[Date] ),
AND (
Table[Date] <= MAX ( Table[Date] ),
DATEADD ( Table[Date], 12, MONTH ) > MAX ( Table[Date] ))))

I share you an extract of my base:

enter image description here

Thanks in advance !!!

Upvotes: 0

Views: 1071

Answers (1)

user18251855
user18251855

Reputation:

Based on the table and code you have shared, it is unclear from where the date filters are being applied. In case you have not done it, I strongly suggest to delete the [Month] and [Month Number] field from your Sales table and keep them in a separate Calendar table, from where the filters should be selected. Then, a simple tweak on you current formula should do the trick:

Rolling Year = 
CALCULATE (  
SUMX ( Table, Table[Sales] ),  
FILTER (  
ALL ('Calendar'[Date] ),  
AND (  
Table[Date] <= MAX ( 'Calendar'[Date] ),  
DATEADD ( 'Calendar'[Date], 12, MONTH ) > MAX ( 'Calendar'[Date] ))))

However you can try with this variation for the code, a little bit optimized so as not to scan your whole Sales table each time:

Rolling Year = 
VAR EndSelectedDate = MAX ( 'Calendar'[Date] )
VAR StartSelectedDate =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        ALL ( 'Calendar'[Year] ),
        'Calendar'[Year]
            = MAX ( 'Calendar'[Year] ) - 1
    )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= EndSelectedDate,
        'Calendar'[Date] > StartSelectedDate
)

Upvotes: 1

Related Questions