dax

[Anonymous]

I have Month number as [Month], sales as Retailing , document_date and i want to display sales of last to last month. and another measure of sales prior to last to last month. These needs to be displayed in a matrix in with there are some other columns so i can not take month column in column directly. 

Month	Retailing
1	22
3	32
1	12
5	34
6	43
2	54
1	54
4	55
3	65
4	66
5	43
3	64
2	32
1	53
4	65
4	76

@Uzi2019 

[Anonymous]

Hi @Anonymous 
first of all , You have to create calendar table and then connect with your table. That calendar table(Table name is calendar) structure below.

after you will create dax measure.
DAX 1:

LAST 1 MONTH = CALCULATE(SUM(Orders[Sales]),DATEADD('calendar'[Date],-1,MONTH))

DAX 2:

LAST 2 MONTH = CALCULATE(SUM(Orders[Sales]),DATEADD('calendar'[Date],-2,MONTH))

DAX 3:

LAST 3 MONTH = CALCULATE(SUM(Orders[Sales]),DATEADD('calendar'[Date],-3,MONTH))

[Uzi2019]

Hi @Anonymous 
Would you please provide the expected output??
dont show the actual data just random data(but correct) in expected output just to get the idea how many columns would be there in matrix visual.

[Anonymous]

Category	Swing Sales	Month
Aircare	37	7
Aircare	30	8
Aircare	28	9
Aircare	25	10
Baby Care	653	7
Baby Care	645	8
Baby Care	664	9
Baby Care	520	10
Fabric & Home Care	1210	7
Fabric & Home Care	1167	8
Fabric & Home Care	1019	9
Fabric & Home Care	1067	10
Fem Care	654	7
Fem Care	677	8
Fem Care	546	9
Fem Care	560	10
Grooming	436	7
Grooming	527	8
Grooming	472	9
Grooming	460	10
Hair Care	383	7
Hair Care	278	8
Hair Care	268	9
Hair Care	301	10
Health Care	841	7
Health Care	911	8
Health Care	673	9
Health Care	804	10
Oral Care	249	7
Oral Care	186	8
Oral Care	187	9
Oral Care	243	10
Personal Care (GIL)	51	7
Personal Care (GIL)	51	8
Personal Care (GIL)	72	9
Personal Care (GIL)	51	10
Personal Care (OS)	13	7
Personal Care (OS)	16	8
Personal Care (OS)	19	9
Personal Care (OS)	14	10
Skin care	20	7
Skin care	30	8
Skin care	28	9
Skin care	52	10

if you select 10 th month then i want to display 10th sales as well as 9, 8,7 
expected result for 9th should be 3976 8th should be 4520. I dont want to add month in the matrix.
measures for last month then last to last month and prior to last to last. 3 measures. i do have document_date as date column but if you can do it with just month that would be gr8. @Uzi2019 

[Uzi2019]

Hi @Anonymous 
Please provide data with date column it would be easier to calculate dax.

[Anonymous]

https://drive.google.com/file/d/1yoDo-kCT5iNlZVribShYUPwIbHWbRyl-/view?usp=sharing

[Uzi2019]

Hi @Anonymous 
Please provide the expected output in form of matrix visual. you can calculate in excel share the that table here to understand how you want to see the data. 
how much data will be there when you want to display in table visual.

I hope you know what I am asking.

[Anonymous]

i have sales of last year, this year , i have shown last month also but i'am not able to show last 2 last month and before that. these data should change dynamically whenever i select a month then these should change automatically rather than manually entering month for each measure. 

[Uzi2019]

Hi @Anonymous 
Based on your data for 7,8,9,10 as month Number.I have created simple formula
I hope this is what you are looking for 
if you select 10 in slicer then 7,8,9,10 should be calcualted.

If I select 9 then 7,8,9 data should be summed up. because you have limited data.
Please create below measure for last 3 months calculation:

Cummuative 1 =

Var N= MAX('Table'[Month])-3

Var A=CALCULATE(SUM('Table'[Swing Sales]),'Table'[Month]>=N && 'Table'[Month]<=MAX('Table'[Month]))

I hope this is what you wanted. 
and If you want something like below image then its different calculation!
Based on your selection your measure should change the value.

[Anonymous]

@lbendlin @amitchandak @Ahmedx @Ritaf1983 @Idrissshatila