Get last 2 years data from measure based on slicer

[haripbi]

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.

[v-yilong-msft · Accepted solution]

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.

[lbendlin]

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