Filtering last day with data

[Anonymous]

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 data from Saturday on Mondays. No matter what I do the measure is showing blank on Mondays.

This is how my measure looks like:

last_day_with_data = 
VAR _yesterday = CALCULATE(
    SUM('Table'[value]), 
    FILTER ('Table', 
    DATEVALUE ('Table'[timestamp]) = DATEVALUE(MAX('Table'[timestamp])-1)))
VAR _daybefore = CALCULATE(
    SUM('Table'[value]), 
    FILTER ('Table', 
    DATEVALUE ('Table'[timestamp]) = DATEVALUE(MAX('Table'[timestamp])-2)))
VAR _monday = [weekday_measure] = "maanantai"
RETURN
IF(_monday, _daybefore, _yesterday)

Can anyone help me with this? 🙂 You can find the pbix in here.

Thank you in advance!

Jules 

[daxer-almighty]

Try to start with this: Power BI on Microsoft Learn | Microsoft Docs

[daxer-almighty]

@Anonymous 

Of course you can't see anything because to see anything you have to filter ONE DATE ONLY. You don't filter for one date in your file. You have a bunch of them selected in the slicer. It's even worse. You are using a column from your fact table as the column for a slicer. This will never work correcttly. As mentioned above: slicing is always done through dimensions, fact tables are always hidden.

[Anonymous]

@Anonymous 

The measure is correct. Something on your side is not. For this to work, you have to hide your fact table and only slice by dimensions (you should almost never slice by anything that's in your fact tables). Mark your Dates table as the Date table in the model.

[Anonymous]

Can't for the time being open the file (I'm at work). This measure will return a value only in case a single date is visible in the current context since you have not defined what to return if there are more dates visible. And, obviously, your Dates must be related to your fact table...

[Anonymous]

The tables are related and I'm using a slicer to filter a single date.

Some other changes are needed...

[Anonymous]

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

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 

[amitchandak]

@Anonymous , Try like this. Not sure if _monday logic is working

last_day_with_data =
var _max = maxx(allselected('Table'), 'Table'[date])
var _min = maxx(filter('Table', 'Table'[Date] <_max), 'Table'[Date])
var _min2 = maxx(filter('Table', 'Table'[Date] <_min), 'Table'[Date]) //use _min2 and _min if needed
return
VAR _yesterday = CALCULATE(
SUM('Table'[value]),
FILTER ('Table',
DATEVALUE ('Table'[Date]) = _max ))
VAR _daybefore = CALCULATE(
SUM('Table'[value]),
FILTER ('Table',
DATEVALUE ('Table'[Date]) = _min))
VAR _monday = [weekday_measure] = "maanantai"
RETURN
IF(_monday, _daybefore, _yesterday)

[Anonymous]

Hi, 

Thanks @amitchandak. I expressed myself poorly. I need to show data from yesterday (on every other day than Monday) or the day before (on Mondays) even though there would be data from the ongoing day also. Your solution is showing data from today. 

Julia