topic Re: Filtering last day with data in DAX Commands and Tips

Filtering last day with data

Anonymous — Thu, 18 Mar 2021 14:30:28 GMT

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

Re: Filtering last day with data

amitchandak — Thu, 18 Mar 2021 14:41:51 GMT

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)

Re: Filtering last day with data

Anonymous — Thu, 18 Mar 2021 15:01:07 GMT

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

Re: Filtering last day with data

Anonymous — Thu, 18 Mar 2021 15:47:10 GMT

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

Re: Filtering last day with data

Anonymous — Thu, 18 Mar 2021 16:15:22 GMT

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

Re: Filtering last day with data

Anonymous — Thu, 18 Mar 2021 17:20:49 GMT

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...

Re: Filtering last day with data

Anonymous — Fri, 19 Mar 2021 09:30:59 GMT

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

Some other changes are needed...

Re: Filtering last day with data

Anonymous — Fri, 19 Mar 2021 10:47:11 GMT

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.

Re: Filtering last day with data

daxer-almighty — Fri, 19 Mar 2021 10:58:30 GMT

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.

Re: Filtering last day with data

daxer-almighty — Fri, 19 Mar 2021 11:06:53 GMT

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