topic Re: Create Measure with LatestDate Minus 1 Year in DAX Commands and Tips

Create Measure with LatestDate Minus 1 Year

natasha519 — Tue, 23 Nov 2021 17:37:36 GMT

I have a column called 'Date' and it has dates all the way till April 2021. I then created a measure called 'Latest Date' with the following formula:

LatestDate = VAR LatestDate = CALCULATE(MAX(Table[Date]), ALL(Table)) RETURN IF (MIN(Table[Date]) = LatestDate, 1,0)

This provides me with a 1 for April 2021 since it is the latest date. I now want to do the same thing but this time I want it to be the latest date minus 1 year, so April 2020.

How can I create a measure that has a 1 for April 2021 (from LatestDate measure) and 1 for April 2020 (LatestDate - 1)?

Re: Create Measure with LatestDate Minus 1 Year

smpa01 — Tue, 23 Nov 2021 18:06:33 GMT

natasha519 can you try htis

Measure = VAR LatestDate = CALCULATE(MAX('Table'[Date]), ALL('Table')) VAR _x = IF (MIN('Table'[Date]) = LatestDate,LatestDate-365) RETURN _x

Re: Create Measure with LatestDate Minus 1 Year

natasha519 — Tue, 23 Nov 2021 18:55:49 GMT

I would want a 1 in the new column, so 1 for April 2020 and then 1 for April 2021, and the rest of the values would be 0

Re: Create Measure with LatestDate Minus 1 Year

natasha519 — Tue, 23 Nov 2021 19:07:23 GMT

I tried this:

LastYear =

VAR LatestDate = CALCULATE(MAX(Provisional_Data[Date]), ALL(Provisional_Data))

RETURN IF (MIN(Provisional_Data[Date]) = LatestDate-365,1,0)

But this only gives me a 1 for April 2020. I now want a 1 in BOTH April 2020 and where the LatestDate =1 so I can use Top N

Re: Create Measure with LatestDate Minus 1 Year

smpa01 — Tue, 23 Nov 2021 20:50:05 GMT

natasha519 do you mean this, pbix is attached

forTOPN = IF ( 'Table'[Date] = CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) ) || 'Table'[Date] = CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) ) - 365, 1, 0 )

Re: Create Measure with LatestDate Minus 1 Year

smpa01 — Thu, 25 Nov 2021 14:10:30 GMT

natasha519 did you have a chance to look into the solution I provided?