Last Year Measure showing against future dates as blank

[Anonymous]

Intro:

I have some GA data which contains revenue by date, amongst other dimensions. It is for the latest 3 weeks of TY and LY, i.e. current week we are in, last week and last week -1 and the same correspoinding weeks for LY (-364) to avoid sampling limits imposed by Google.

This GA data's date column is connected onto my date table's (yes it is marked as one 😉) and also to a PY Date column as an inactive relationship:

I have created a measure for TY and LY, but the LY measure doesn't behave how I'd like, measure below:
LY measure:

CALCULATE(
    sum('FACT & DIM Google Analytics'[Revenue])
    ,USERELATIONSHIP('FACT & DIM Google Analytics'[Date],'DIM Calendar'[PY Date])
)

I use the 'UseRelationship' function this way as it allows me to change how the corresponding LY date is calculated (e.g. if Finance change how they want to handle leap years etc - yes this has happened 😣).

Problem:

The downside of this is 2 fold:

1. My date table contains dates for all of 2023, but my source data does not. This means that the measure calculates dates from this year as valid LY dates for dates in the future that haven't happened yet, because they reside in my date table. However, becuase the source GA data doesn't have these dates it just shows them against a blank() date, as shown below, and also shows the LY total as the same as TY, which is frustratingly correct if we include these erronious future dates:

2. If I try and code around this, using the below (or any variation I can think of like '>=today()'), it removes the erronious LY values in the future, but leaves the total the same:

if(CALCULATE(SUM('DIM Calendar'[Date]))=blank(),blank(),
CALCULATE(
    sum('FACT & DIM Google Analytics'[Revenue])
    ,USERELATIONSHIP('FACT & DIM Google Analytics'[Date],'DIM Calendar'[PY Date])
))

Ideal solution:

I don't want it show the erronious future weeks in the LY column, and for the LY column to sum up only those visible/included LY weeks in the total at the bottom.
I feel like it is something to do with 'HasOneValue()' do 'x' else do 'y'...

Thanks in advance 

[PurpleGate]

For the last year measure, could you try

measure = (CALCULATE(SUM('FACT & DIM Google Analytics'[Revenue]),SAMEPERIODLASTYEAR('Dim Calendar'[Date])))

Perhaps you should also have your relationship between DATE and FCT table as a ( 1-* ) with the direction from Date towards your Fct.Table

In your table, are you using 'Date' from your Dim Calendar?

In the visual panel, you can filter out blank dates. That works for me to then only sum up the visible values.

[Anonymous]

Thanks for coming back to me, but 'SamePeriodLastYear' would result in -365 instead of -364, unless I've missed something?

It is already a 1-*, with the Date table on the '1' side of the relationship.

The only issue with filtering out the blank dates is that it won't work in 'Analyse in Excel', as users will forget...I probably should have mentioned that. Ideally, I want it to work as part of the measure so when it is dragged into a Pivot Table by an end user, it calculates correctly without people needing to complete extra steps as most users won't understand the intracacies of how the data model works and will question the data, thus eroding the data democratisation culture.