Forum Discussion
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
you can try 'lydate = MAX(Date[Dates])-365' or you can try the DATEADD function
3 Replies
you can try 'lydate = MAX(Date[Dates])-365' or you can try the DATEADD function
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/
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?
