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 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)
Need Help

2 Replies

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