Convert measure for the same calculation for the previous year.

[Mike22]

Hello Friends, 

I have the measure below to calculate YTD number of trips based on the filter "submission date" if there are multiple selection the calculation will be done on the current year. If one selection only it will take the year selected. "Submission date" is a slicer on the page.

As this is a KPI i would like to have another measure doing the same exact calculation but for the year before to have the comparison. If many years selected in "submission date" it should calculate for the current year -1 if a year is selected it should be done for the selected year -1. 

Of course the slicer filters out the data so I would need to use the ALL. Just not sure how to. 

Thanks in adance for the help!

YTD #Trips =

var manyyear= TOTALYTD(DISTINCTCOUNT(Travel_Details_All[Trip Number]),Travel_Details_All[Trip Start Date],Travel_Details_All[Trip Start Date]<TODAY())

var oneyear= CALCULATE(DISTINCTCOUNT(Travel_Details_All[Trip Number]),DATESBETWEEN(Travel_Details_All[Trip Start Date].[Date],STARTOFYEAR(Travel_Details_All[Trip Start Date]),ENDOFYEAR(Travel_Details_All[Trip Start Date])))

return

IF(SELECTEDVALUE(Travel_Details_All[Submission Date].[Year],0)=0,manyyear,oneyear)

[v-frfei-msft]

Hi @Mike22 ，

The key point here should be a slicer table without relating to the fact table. Then we can create a measure like this to the excepted KPI.

Measure =
VAR this_year =
    YEAR ( TODAY () ) - 1
VAR countr =
    COUNTROWS ( Slicer )
VAR sele =
    SELECTEDVALUE ( Slicer[Submission date] ) - 1
RETURN
    IF (
        countr = 1,
        CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', 'Table'[Year] = sele ) ),
        IF (
            countr > 1,
            CALCULATE (
                SUM ( 'Table'[value] ),
                FILTER ( 'Table', 'Table'[Year] = this_year )
            )
        )
    )

[Mike22]

HI @v-frfei-msft 

Thank you for the quick reply. Just a few clarifications please. 

VAR countr =
    COUNTROWS ( Slicer )

What is slicer supposed to be? The current slicer is currently derived from the travel submission date. 

Let me just show you how the data is structured

trip number	trip start date	trip end date	submission date
1	01/02/2019	05/02/2019	25/01/2019
2	06/06/2018	08/06/2018	04/05/2018
3	08/08/2018	11/08/2018	30/07/2018
4	06/09/2017	18/09/2017	08/08/2017

If submission year selected is 2018 the count for 2018 will be 2 (this is in the measure I shared with you). I need a measure that will calculate 2017 when 2018 is selected. If 2018 and 2017 are selected it will count 2018 as it is current year (2019)-1. 

The count is done based on the "trip start date column" not the submission date. So if a trip is submitted in 2018 and start date in 2019 it will count against the 2019 data. 

Hope this helps and thanks again.