DAX measure for previous year/month value

[zervino]

I have a table with 5 columns:

Plan	Year	Month	Year/Month	Sales
2024 FC	2024	01	202401	100
2024 FC	2024	02	202402	75
2024 Actuals	2024	01	202401	120
2025 FC	2025	01	202501	105
2025 Actuals	2025	01	202501	140

I want to add a measure to show the value of the same period but previous year/month. For example from previous table:

Plan	Year	Month	Year/Month	Sales	Previous Sales
2024 FC	2024	01	202401	100
2024 FC	2024	02	202402	75
2024 Actuals	2024	01	202401	120
2025 FC	2025	01	202501	105	120
2025 Actuals	2025	01	202501	140	120

I use the current formula:

Previous Sales = 
CALCULATE([Sales], 
    FILTER(
        ALL('Dates'),
            'Dates'[Month]=SELECTEDVALUE('Dates'[Month])
    ),
    'Dates'[Year]=SELECTEDVALUE('Dates'[Year]) - 1
)

* I have joined my fact table to a 'Dates' table on Year/Month column.

The formula works, the problem is that when I create a visual of a table and I filter by year "2025 Actuals" only, the Previous Sales column appears empty. It only gets populated if I also have "2024 Actuals" included.

Is there a way to avoid having to include the previous year?

[ahmedoye]

I see, you can create date columns as Calculated columns from what you have and use CALCULATE([Sales], SAMEPERIODLASTYEAR(Dates[Date])). 

You can create column in your Sales Table as 

DateNew = DATE(VALUE(LEFT(Table1[Year],4)), VALUE(RIGHT(Table1[Year/Month])),1)
Then create Date Column in your Date table as 

DateCol = DATE('Date'[Year], 'Date'[Month], 1)

Then use those 2 columns to define the relationship between both tables and rewrite your measure with CALCULATE and SAMEPERIODLASTYEAR as I have indicated above.

[ahmedoye]

The best way to do this will be to have a Date Column (with date format) in your Sales Table. And then let it be related to the Date Column in your Date Table.

Then you can write your formula as CALCULATE([Sales], PREVIOUSMONTH(Dates[Date]))

If this works for you, kindly mark as answer to allow anyone with similar issues find the solutions quicker.

[zervino]

I don't have Date per se, I have year and month columns.

I need the sales of the same month, but for previous year for a specific plan (eg. Actuals, Forecast)

[Ahmedx]

pls try this

Previous Sales = 
VAR _LasttYear =  SELECTEDVALUE('Dates'[Year]) - 1
VAR _CurrentMpnth  =  SELECTEDVALUE('Dates'[Month])
RETURN

CALCULATE([Sales], 
  'Dates'[Month]=_CurrentMpnth&&
    'Dates'[Year]=_LasttYear
)

[zervino]

The result appears blank 😞

[Ahmedx · Accepted solution]

pls try

Previous Sales = 
VAR _LasttYear =  SELECTEDVALUE('Dates'[Year]) - 1
VAR _CurrentMpnth  =  SELECTEDVALUE('Dates'[Month])
RETURN

CALCULATE([Sales], 
  'Dates'[Month]=_CurrentMpnth&&
    'Dates'[Year]=_LasttYear,
REMOVEFILTERS()
)