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

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • 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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

       

      Some other changes are needed...

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    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.