Cumulative sum until a date and Sum for last days

[lmagazzi]

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?

[ValtteriN · Accepted solution]

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/

[lmagazzi]

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