Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

DAX Problem: Previous Year, no Date Table for time intelligence

I want to do a calculated measure which shows me the total sales for December of the previous year.

I do not have a date recognized table to use, so i want to use a numeric calculation for the year. (because of index optimization reasons)

Can someone help me with the DAX for this measure?

Total Sales DEC LY = 
CALCULATE([Total Sales],
DIM_YEAR[Year]= .......
DIM_MONTH[Month]=12
)

Anonymous

Just found the way of doing it; I did not know I needed to specifically use a variable

Total Sales DEC LY = 
VAR LY = LASTNONBLANK(DIM_YEAR[Year],1)-1
Return  
CALCULATE([Total Sales],
DIM_YEAR[Year]=LY,
DIM_MONTH[Month]=12
)

.

4 Replies

parry2k
Super User
7 years ago
Anonymous not sure why you don't want date dimension in your model, anyhow try this.

Total Sales DEC LY = CALCULATE([Total Sales], DIM_YEAR[Year]= DIM_YEAR[YEAR] - 1 DIM_MONTH[Month]=12 )
- Anonymous
  Not applicable
  7 years ago
  Just found the way of doing it; I did not know I needed to specifically use a variable
  
  Total Sales DEC LY = VAR LY = LASTNONBLANK(DIM_YEAR[Year],1)-1 Return CALCULATE([Total Sales], DIM_YEAR[Year]=LY, DIM_MONTH[Month]=12 )
  .
  - SpaceJunkie
    New Member
    3 years ago
    This seems like a calculated column; Did you run it as a Measure?
- Anonymous
  Not applicable
  7 years ago
  Thanks for the suggestions. I had tested something like that, but the something argument for the DIM[Year] = someting cannot be a column reference, has to be a measure or a variable.