cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PedroReis Regular Visitor
Regular Visitor

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
)
 

 

problem.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 
1 ACCEPTED SOLUTION

Accepted Solutions
PedroReis Regular Visitor
Regular Visitor

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

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
)

3 REPLIES 3
Super User
Super User

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

@PedroReis 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
)




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




PedroReis Regular Visitor
Regular Visitor

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

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
)

PedroReis Regular Visitor
Regular Visitor

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

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.