Forum Discussion
Solved
Last month function with dax
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 |
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")
1 Reply
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")