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