cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Convert measure for the same calculation for the previous year.

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)
2 REPLIES 2
Community Support

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 )
)
)
)
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper III

```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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.