cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

## 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
)```

1 ACCEPTED SOLUTION

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 4
Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

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
)```

New Member

This seems like a calculated column; Did you run it as a Measure?

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors