Forum Discussion

Mike22's avatar
Mike22
Icon for Helper III rankHelper III
6 years ago

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 ...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Mike22
Icon for Helper III rankHelper III
6 years ago

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 numbertrip start datetrip end datesubmission date
101/02/201905/02/201925/01/2019
206/06/201808/06/201804/05/2018
308/08/201811/08/201830/07/2018
406/09/201718/09/201708/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.