Forum Discussion

Mike22's avatar
Mike22
Helper 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
Community 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
    Helper 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.