Forum Discussion

Raulx_7's avatar
Raulx_7
Icon for Helper I rankHelper I
6 years ago
Solved

Last year from max date

Hi, I was doing some calculations, and in this one I use a filter to select the max date :

 

date = MAX(Date[Dates]), 

 

Now I need te value from the same date but in the last year, I have tried with 

date =  SAMEPERIODLASTYEAR (MAX(Date[dates]))

But it gives me an error with dates

 

Is there a way to solve this or I need to develop another solution

Need Help
Tips and Tricks
Tutorial Requests
  • jbirco's avatar
    jbirco
    6 years ago

    you can try 'lydate = MAX(Date[Dates])-365' or you can try the DATEADD function

3 Replies

  • jbirco's avatar
    jbirco
    Icon for Resolver I rankResolver I
    6 years ago

    you can try 'lydate = MAX(Date[Dates])-365' or you can try the DATEADD function

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    6 years ago

    This is not valid syntax - you seem to be mixing inbuilt time intelligence with custom time intelligence. It seems you have a calendar table. The date from the calendar table needs to be in your visual. Then you could write this

     

    =calculate([measure],sameperiodlastyear(date[date]))

     

    read my article here https://exceleratorbi.com.au/dax-time-intelligence-beginners/

     

     

  • Ryks's avatar
    Ryks
    Frequent Visitor
    4 years ago

    I have the same problem and cannot get it to work.

    Sameperiodlast year does not work.  OpenCurrentYTD is correct

     

    OpenCurrentYTD =

    Var Segmentvar = AllSELECTED(WorkersComp[Segment])

    Var Divisionvar = AllSELECTED(WorkersComp[Sub-Segment])

    Return

    CALCULATE(Count(WorkersComp[Solvinjury ID]),WorkersComp[Segment] in Segmentvar,WorkersComp[Sub-Segment]in Divisionvar,

    Filter(ALL(WorkersComp),( WorkersComp[InclusiveStatus]="Yes" && WorkersComp[DateSubmittedME] <= max('Calendar'[Date]) && WorkersComp[DateSubmittedME] <> Blank() && (('WorkersComp'[ClosedME] = Blank()) || WorkersComp[ClosedME] > max('Calendar'[Date]) ))))

     

    Tried

    PrevPeriod =

    Var Segmentvar = AllSELECTED(WorkersComp[Segment])

    Var Divisionvar = AllSELECTED(WorkersComp[Sub-Segment])

    Var PrevPMax = EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),-12)

    Return

    CALCULATE(Count(WorkersComp[Solvinjury ID]),WorkersComp[Segment] in Segmentvar,WorkersComp[Sub-Segment]in Divisionvar,

    Filter(ALL(WorkersComp),(WorkersComp[InclusiveStatus]="Yes" && WorkersComp[DateSubmittedME] <=PrevPMax && WorkersComp[DateSubmittedME] <> Blank() && (('WorkersComp'[ClosedME] = Blank()) || WorkersComp[ClosedME] >PrevPMax))))

     

    And Tried

    KeepFilters =

    Var Segmentvar = AllSELECTED(WorkersComp[Segment])

    Var Divisionvar = AllSELECTED(WorkersComp[Sub-Segment])

    Var PrevPMax = EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())),-12)

    Return

    CALCULATE(Count(WorkersComp[Solvinjury ID]),WorkersComp[Segment] in Segmentvar,WorkersComp[Sub-Segment]in Divisionvar,

    Keepfilters(WorkersComp),(WorkersComp[InclusiveStatus]="Yes" && WorkersComp[DateSubmittedME] <=PrevPMax && WorkersComp[DateSubmittedME] <> Blank() && (('WorkersComp'[ClosedME] = Blank()) || WorkersComp[ClosedME] >PrevPMax)))

     

    Results:

     

    Any ideas?

    MattAllington