Last month function with dax

[dokat]

Hi,

I have a table that i am trying to add a column based on a condition. In the New column i want it to show "Last Year", "Last Month" and "YTD" based on the calue under CY columns.

I have a formula that works for the most part except last month. It doesnt return latest months value based CY. Can anyone help with below?

New Column = var max_date = calculate(max(Tier[CY]),all()) return switch(true(),

Tier[CY]= max_date,"YTD",

and(MONTH(Tier[CY]) = month(max_date),YEAR(Tier[CY]) = YEAR(max_date)),"Last Month",

Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")

Below is what i want final table to look like

CY	Tier	Sales	Volume	New Column
12/31/2018	2	25	125
12/31/2019	2	50	200
12/31/2020	1	100	500
12/31/2021	1	200	600	Last Year
1/1/2021	3	300	700
1/1/2022	3	400	800
2/28/2021	3	500	900
2/1/2022	3	600	1000	Last Month
2/28/2022	3	1000	1800	YTD

[dokat · Accepted solution]

Corrected data table and below is working now

New Column = var max_date = calculate(max(Tier[CY]),all()) return switch(true(),

Tier[CY]= max_date,"YTD",

and(MONTH(Tier[CY]) = month(max_date),YEAR(Tier[CY]) = YEAR(max_date)),"Last Month",

Tier[CY]= DATE ( YEAR (max_date)-1, 12, 31 ),"Last Year")