topic Re: Cumulative sum until a date and Sum for last days in DAX Commands and Tips

Cumulative sum until a date and Sum for last days

lmagazzi — Wed, 02 Feb 2022 17:46:35 GMT

Hi everybody,
I'm new to Power BI and there are a few of DAX's issues which are not so easy to understand; although I read some old posts I was not able to find an answer to my question.
This is my problem:
I've got a table like the one below

Date(YYMMDD)	ProductType	DailySales
211101	A	11
211101	B	10
211101	C	21
211102	A	8
211102	B	22
211102	C	1
211103	A	14
211103	B	2
211103	C	13
211104	A	2
211105	A	4
211106	A	3
211107	A	8
211108	A	20

and so on for thousands of rows; for every pair of Data+Product Type I would like to have in the same table or in a new one something like the following:

Date(YYMMDD)	ProductType	DailySales	CumulatedSales	SalesInLast7Days
211101	A	11	11	11
211101	B	10	10	10
211101	C	21	21	21
211102	A	8	19	19
211102	B	22	32	33
211102	C	1	22	22
211103	A	14	33	33
211103	B	2	34	34
211103	C	13	35	35
211104	A	2	35	35
211105	A	4	39	39
211106	A	3	42	42
211107	A	8	50	50
211108	A	20	70	59

and so on...
-CumulatedSales is the sum the sales of the product type in the current row from first day until the current day , for every row)
- SalesInLast7Days is the sum of the sales of the product type in the current row in last 7 days (sales of current date of that product type plus sales of previous day and so on untile the previous 6th day, as you can see it differs from the CumulatedSales column after the first week).
I dont't know if these new columns would be Calculated Columns, or Measures, or they must be in an another table ...In Excel it's rather simple with SumIfS but in DAX it seems I need to make a filter for every rows ... but how can I filter sales data in other rows in DAX?
Could someone please kindly help me to solve this issue?

Re: Cumulative sum until a date and Sum for last days

ValtteriN — Wed, 02 Feb 2022 17:52:50 GMT

Hi,

I recommend creating measures. For RT the baisc pattern is like this:
var maxdate = MAX('CALENDAR'[Date]) return
CALCULATE(SUM(Table[Value]),ALL(CALENDAR),'CALENDAR'[Date]<=maxdate)

Also check this post by SQLBI explaining the logic behind the DAX: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

For last 7 you can just modify the previous dax a bit:

var maxdate = MAX('CALENDAR'[Date]) return
CALCULATE(SUM(Table[Value]),ALL(CALENDAR),'CALENDAR'[Date]<=maxdate,'CALENDAR'[Date]>maxdate-8)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Cumulative sum until a date and Sum for last days

lmagazzi — Wed, 02 Feb 2022 21:39:24 GMT

It works! Thank you very much, i tried something similar but i used the FILTER instruction, not the ALL and the other conditions without Filter . I have to study 🙂
Thanks a lot