Forum Discussion

natasha519's avatar
natasha519
Icon for Helper II rankHelper II
4 years ago
Solved

Create Measure with LatestDate Minus 1 Year

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)?
  • smpa01's avatar
    smpa01
    4 years ago

    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
)

     

     

     

5 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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

     

    • natasha519's avatar
      natasha519
      Icon for Helper II rankHelper II
      4 years ago

      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

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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
)

         

         

         

  • natasha519's avatar
    natasha519
    Icon for Helper II rankHelper II
    4 years ago

    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