topic Re: Time intelligence - value of last month of previous year in DAX Commands and Tips

Time intelligence - value of last month of previous year

Bobass75 — Thu, 01 Feb 2024 21:44:16 GMT

Maybe it's simple, but I'm stuck - in this context, when selecting one month of current year (in eg Feb 2011) , I need to show the value from the last month of the previous year (so 150, Dec 2010). Thank you in advance.

Re: Time intelligence - value of last month of previous year

aj1973 — Thu, 01 Feb 2024 22:38:49 GMT

Hi Bobass75

You don't need the use of Time intelligence for this. You need to get the Value in December of the Previous Year.

Try to adjust this please.

Your Measure =
VAR _Year = SELECTEDVALUE(Date_table(YEAR))

VAR _PY_Year = _Year - 1

VAR _Result = CALCULATE( CU, Date_table(YEAR) = _PY_Year , Date_table(Month) = 12)
RETURN

_Result

Re: Time intelligence - value of last month of previous year

Bobass75 — Sun, 04 Feb 2024 09:03:13 GMT

Hello
I wrote the formula and it does not work

Value - end of PY =

VAR _Year = SELECTEDVALUE(Date_table[Year Number])

VAR _PY_Year = _Year - 1

VAR _Result = CALCULATE( [CU], (Date_table[Year Number] = _PY_Year) , Date_table[Month Number] = 12)

RETURN

_Result

Re: Time intelligence - value of last month of previous year

talespin — Sun, 04 Feb 2024 10:29:41 GMT

hi Bobass75 ,

Since you have a slicer on date, you need to remove filter on Date when using CALCULATE. I have a Date table and Fact Sales, using below measure to calculate sales for last month previous year.

Sales Last Mth PY =

VAR _Year = SELECTEDVALUE(DimDate[CalendarYear])

RETURN CALCULATE( [MSalesAmount], REMOVEFILTERS(DimDate), DimDate[CalendarYear] = (_Year - 1) && DimDate[MonthNumberOfYear] = 12)

Re: Time intelligence - value of last month of previous year

Anonymous — Mon, 04 Mar 2024 09:52:47 GMT

Hi Bobass75

talespin GOOD ANSWER!

If your problem hasn't been solved yet, you can also try this.

1. Create a calculated table as the slicer

Date = VALUES('Table'[Date])

2. Create a measure

Measure = VAR _1 = YEAR(SELECTEDVALUE('Date'[Date])) VAR _month = 12 RETURN IF(MAX([Date]) = SELECTEDVALUE('Date'[Date]) || YEAR(MAX([Date])) = _1 - 1 && MONTH(MAX([Date])) = _month, 1, 0)

3. Put the measure into the visual-level filters, set up show items when the value is 1.

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.