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(Tab...
  • 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
)

     

     

     

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
)

       

       

       

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

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