Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## 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
1 ACCEPTED SOLUTION
Post Prodigy

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 1
Post Prodigy

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")

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors