CALCULATING COLUMN Rolling 20week Sales

[joshua1990]

Hi experts!

I have a Dimensional Table that contains all Articles for each department:

Article	Department
ABC	1
EDFG	2

Then I have a transactional table:

Year-Week	Article	Sales
2022-01	ABC	500
2022-02	ABC	600
2022-03	ABC	400

Now I would like to add a calculated column into the dimensional table that shows me all Sales for each Article for the past 20 weeks.

I now, I general we would do that as a simple Dax Measure in a matrix visual.

But here is the ask as a calculated column. How would you do that?

[v-zhangti · Accepted solution]

Hi, @joshua1990 

You can try the following methods. I simulated some data briefly and hope it will meet your requirements.

Column:

Year-Week = YEAR([Date])&"-"&WEEKNUM([Date],2)

Rolling 20 week sales = 
VAR N1 =
    SUMMARIZE (
        FILTER ( Transactional, [Year-Week] <= MAX ( Transactional[Year-Week] ) ),
        [Date],
        "Sum",
            CALCULATE (
                SUM ( Transactional[Sales] ),
                FILTER ( Transactional, [Article] = EARLIER ( Dimensional[Article] ) )
            )
    )
VAR N2 =
    TOPN ( 20, N1, [Date], DESC )
RETURN
    SUMX ( N2, [Sum] )

Please see the attachment for details.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Assuming that you have a date table with a start of week column, you could add a column as

Rolling 20 week sales = 
var startDate = LOOKUPVALUE('Date'[start of week], 'Date'[Date], TODAY())
return CALCULATE( SUMX( RELATEDTABLE( 'Transactions'), 'Transactions'[Sales]),
'Date'[Date] >= startDate
)

This would be recalculated whenever the data is refreshed