cancel
Showing results for
Did you mean:

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

Continued Contributor

## do a calculate table and filter by the last month

I have a fact table and a date table and would like to filter the fact table by using calculate table function and get data for the last month. I have some ideas as how to implement this but I need this to work at time when we are in january and want data for december the month before.

What I have already which will work for every month except january.

``````Last Month Orders =
CALCULATETABLE(
'Orders',
'Date'[Month No] = MONTH(TODAY()) - 1 && 'Date'[Year] = YEAR(TODAY())
)``````

But if we are in janurary it will show orders for december 2023 which I would not want but instead have orders for december 2022.

1 ACCEPTED SOLUTION
Helper II
``````You can use the IF function to determine if the current month is January, if so, subtract 1 from the year, otherwise use the current year. Here is the revised code:

Last Month Orders =
CALCULATETABLE(
'Orders',
'Date'[Month No] = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1) && 'Date'[Year] = IF(MONTH(TODAY()) = 1,  YEAR(TODAY()) - 1, YEAR(TODAY()))
)

This way, no matter what the current month is, you can get the last month's data correctly.``````
Helper II
``````You can use the IF function to determine if the current month is January, if so, subtract 1 from the year, otherwise use the current year. Here is the revised code:

Last Month Orders =
CALCULATETABLE(
'Orders',
'Date'[Month No] = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1) && 'Date'[Year] = IF(MONTH(TODAY()) = 1,  YEAR(TODAY()) - 1, YEAR(TODAY()))
)

This way, no matter what the current month is, you can get the last month's data correctly.``````

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