Forum Discussion

eaj's avatar
eaj
Helper I
1 year ago

Last years data based on individual GoLiveDates

We're conducting a pilot project, and all units have different GoLiveDates. Management wants to track the revenue impact by comparing "Revenue 4 weeks pre-implementation vs. LY" and "Revenue post-implementation vs. LY". All these different GoLiveDates are stored in a dim_table

 

As a step towards the final goal, I've created a measure that works well for "Revenue post-implementation CY". However, I'm struggling to adapt it for the LY version.

I've attached my attempt.

Any help would be greatly appreciated!

 

Revenue after GoLiveDate CY = 
SUMX(
    FILTER(
        Dim_CallCenterClinics,
        Dim_CallCenterClinics[FirstInvoiceDate] <= DATE(2024,10,1) - 365 - 28 
--To make sure we compare same amount of units this vs last year (they ha
    ),
    VAR DepartmentID = Dim_CallCenterClinics[ID_Department]
    VAR GoLIVE = Dim_CallCenterClinics[GoLiveDate]
    VAR OtherClinics = DATE(2024,10,1) --If the clinic still hasn't gone live with the PilotProject, 2024-10-01 should be used as a breaking point for pre vs. post. 
    VAR StartDate = COALESCE(GoLIVE, OtherClinics)

    RETURN
        CALCULATE(
            SUM('Fact - BillingInvoice'[SalesExWAT]),
            'Fact - BillingInvoice'[ID_Department] = DepartmentID,
            'Fact - BillingInvoice'[InvoiceDate] >= StartDate
        )
    )

 


This is my attempt at an LY measure, but it only works if last year's dates are included in the date slicer selection. If I select only the relevant dates for this year, it returns blank.

 

Revenue after GoLiveDate LY = 

SUMX(
    FILTER(
        Dim_CallCenterClinics,
        Dim_CallCenterClinics[FirstInvoiceDate] <= DATE(2024,10,1) - 365 - 28
    ),
    VAR DepartmentID = Dim_CallCenterClinics[ID_Department]
    VAR GoLIVE = Dim_CallCenterClinics[GoLiveDate]
    VAR FirstInvoiceDate = Dim_CallCenterClinics[FirstInvoiceDate]
    VAR OtherClinics = DATE(2024,10,1)
    VAR StartDate = COALESCE(GoLIVE, OtherClinics)

    RETURN
    CALCULATE(
        SUM('Fact - BillingInvoice'[SalesExWAT]),
        'Fact - BillingInvoice'[ID_Department] = DepartmentID,
        'Fact - BillingInvoice'[InvoiceDate] >= StartDate - 365,
        'Fact - BillingInvoice'[InvoiceDate] <= MAX(Dim_Date[Date]) -365
    )
)

 

 


As you can see, the LY measure only works if last year's dates are included in the slicer.

 

 

 




Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    eaj , In such case you need use all or removefilter and that works best when you use data table joined with you date.

    Post that you can also use measures like

     

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))


    week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

    • eaj's avatar
      eaj
      Helper I
      1 year ago

      Thanks, I do have a date table in my model and I've tried adding SAMEPERIODLASTYEAR() in my measure. But it's still not working. To confirm I do In fact have data I've added a Test column with just:

      Test = CALCULATE(SUM('Fact - BillingInvoice'[SalesExWAT]), SAMEPERIODLASTYEAR('Dim - Date'[Date]))


      I've also tried this without success (which would be my to-go-to solution normally)

      Test2 = CALCULATE([Revenue after GoLiveDate CY], SAMEPERIODLASTYEAR('Dim - Date'[Date]))


      Here is an attempt at changing my code:

      SUMX(
    FILTER(
        Dim_CallCenterClinics,
        Dim_CallCenterClinics[FirstInvoiceDate] <= DATE(2023,9,4) 
    ),
    VAR DepartmentID = Dim_CallCenterClinics[ID_Department]
    VAR GoLIVE = DATEADD(Dim_CallCenterClinics[GoLiveDate], -1, YEAR) --ADDED, One year before GoLiveDate
    VAR OtherClinics = DATE(2023,10,1) --CHANGED, Adjust to LY 2023 insted of 2024 
    VAR StartDate = COALESCE(GoLIVE, OtherClinics)

    RETURN
    CALCULATE(
        SUM('Fact - BillingInvoice'[SalesExWAT]),
        'Fact - BillingInvoice'[ID_Department] = DepartmentID,
        'Fact - BillingInvoice'[InvoiceDate] >= StartDate,
        SAMEPERIODLASTYEAR('Dim - Date'[Date]) --ADDED
    )
)