topic Re: CALCULATING COLUMN Rolling 20week Sales in DAX Commands and Tips

CALCULATING COLUMN Rolling 20week Sales

joshua1990 — Thu, 31 Mar 2022 09:34:30 GMT

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?

Re: CALCULATING COLUMN Rolling 20week Sales

johnt75 — Thu, 31 Mar 2022 11:11:10 GMT

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

Re: CALCULATING COLUMN Rolling 20week Sales

v-zhangti — Mon, 04 Apr 2022 08:36:38 GMT

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.