Forum Discussion
dax
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 |
9 Replies
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.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. Uzi2019Hi Anonymous
Please provide data with date column it would be easier to calculate dax.
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))
