Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Last Year Measure showing against future dates as blank

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:

Power BI LY Problem Relationships.jpg


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:

Power BI LY Problem.jpg

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])
))

 

Power BI LY Problem Future Dates Fixed.jpg

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 

2 REPLIES 2
PurpleGate
Resolver III
Resolver III

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
Not applicable

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors