topic Re: Get last 2 years data from measure based on slicer in DAX Commands and Tips

Get last 2 years data from measure based on slicer

haripbi — Mon, 22 Apr 2024 13:20:20 GMT

Hi All,

Need some help on the below dax statement.

Requirement:

Slicer = year (example 2023)
Table = Fact_Availability

Need count of equipmentno with filter as HireAvailability = "On Hire" for the last 2 years based on year from slicer.

Tried below but no luck.

Measure =
VAR StartDate = FIRSTDATE(Fact_Availability[CalendarDate])
VAR StartDateLY = DATEADD(StartDate,-2,YEAR)
VAR EndDate = LASTDATE(Fact_Availability[CalendarDate])
RETURN
CALCULATE(
COUNT('Fact_Availability'[EquipmentNo]),
'Fact_Availability'[HireAvailability]="On Hire",
'Fact_Availability'[CalendarDate] >= StartDateLY && 'Fact_Availability'[CalendarDate] <= EndDate
)

The above measure returns only 2023 data because the year slicer has 2023 selected.

When i check variable values individually it shows "StartDateLY" = 2021 and EndDate = 2023 which is correct.

but the above measure is rendering only 2023 data as the filter is 2023.

I need a dax statement which takes input from slicer as 2023 and gets the count of equipment numbers from 2021 till 2023.

Re: Get last 2 years data from measure based on slicer

lbendlin — Mon, 22 Apr 2024 20:51:41 GMT

Your slicer needs to be fed from a disconnected table. Then you use measures to sense its value and compute the expected outcome.

Re: Get last 2 years data from measure based on slicer

Anonymous — Tue, 23 Apr 2024 06:20:16 GMT

Hi haripbi ,

lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

I create a table as you mentioned.

Then I create a new table. It is used as slicer.

Table = VALUES('Fact_Availability'[Year])

Then I create a measure and you will get what you want.

MEASURE = VAR _Slicer = MAX ( 'Table'[Year] ) RETURN IF ( YEAR ( MAX ( 'Fact_Availability'[CalendarDate] ) ) >= _Slicer - 2 && YEAR ( MAX ( 'Fact_Availability'[CalendarDate] ) ) <= _Slicer, 1 )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.