Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
For the last year measure, could you try
measure = (CALCULATE(SUM('FACT & DIM Google Analytics'[Revenue]),SAMEPERIODLASTYEAR('Dim Calendar'[Date])))
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.
User | Count |
---|---|
16 | |
15 | |
14 | |
12 | |
11 |
User | Count |
---|---|
19 | |
15 | |
14 | |
11 | |
9 |