Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Filtering last day with data

Hi,   I'm trying to build a measure that shows the data either from yesterday or the day before depending on the day of the week. Since there are no new data uploads on Sunday, I want to show the d...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Anonymous 

 

For such calculations (and for many, many more reasons) you should have a Date table in your model. Cramping everything into one table is a sure way to fail miserably without even knowing when and where. Please refer to this article to know what a good model suitable for PBI should look like.

 

Here's a solution for the times when your model is correct:

 

[Last Day With Data] =
// Your 'Dates' table has to have
// fields like ShortDayName (Mon, Tue,...),
// LongDayName (Monday, Tuesday),
// WeekDayNumber (Monday = 1, Tuesday = 2,...)
// and more... It should also be marked as a Date
// table in the model.
var CurrentDay =
    SELECTEDVALUE( 'Dates'[Date] )
var IsMonday =
    SELECTEDVALUE( 'Dates'[WeekDayNumber] ) = 1
var LastDayWithData = CurrentDay - 1 - int( IsMonday )
var Result =
    CALCULATE(
        [Your Base Measure],
        'Dates'[Date] = LastDayWithData
    )
RETURN
    Result
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

Thank you for your suggestion. You're right about the date table. I should really start using it every time.

 

There's still something wrong with the measure because it is returning blank. If you have time and energy I would really appreciate if you could take a look at the pbix file. Your measure can be found with the name last_day_with_data3.

last_day_with_data3 = 
var _CurrentDay = SELECTEDVALUE( 'Dates'[Date] ) 
var _IsMonday = SELECTEDVALUE( 'Dates'[DayOfWeekNumber] ) = 1 
var _LastDayWithData = _CurrentDay - 1 - int( _IsMonday ) 
var _Result = CALCULATE(
    SUM('Table'[value]), Dates[Date] = _LastDayWithData)
RETURN 
_Result