do a calculate table and filter by the last month

[akhaliq7]

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. 

[Mrxiang · Accepted solution]

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.