Filter in measure does not work properly exceptionally in the last year

[Jalalmn]

Hi

I have created a measure that calculates the income in terms of year to date.
It works properly for all years in my data, but only in the last year the result is not correct. I have digged into my data and understood that it does not take into effect the last few days in the latest month (which is in May 2024). For previous years it the results of absolutely correct. Only in the year 2024 the results are wrong!

Why it works correctly for all years except for 2024?? why it misses the last few days?

GBV = VAR year_in_context = SELECTEDVALUE('Date'[Year])

      VAR month_in_focus = MONTH(TODAY())

      VAR day_in_focus = DAY(TODAY())

      VAR last_day = DATE(year_in_context, month_in_focus, day_in_focus)

      VAR first_day = DATE(year_in_context, 1, 1)

      VAR Result =  CALCULATE(

            SUM('bookings'[Price]),

                FILTER(

                'bookings',

                'bookings'[BookingDate] >= first_day &&

                'bookings'[BookingDate] <= last_day

                ))

    RETURN Result

Many thanks in advance for your help!

[Jalalmn]

Hi Liu,

Late at night I understood where the problem come from 🙂

Actually I need ytd of income, not the income till the end of month. So it depends on the current day.

The problem was happening because my date table had stopped gettin updated to include the last few days into the dates 🙂

Many thanks anyway,

Jalal

[v-yangliu-msft · Accepted solution]

Hi  @Jalalmn ,

Are you referring to a maximum date of the end of the current month .

You may consider using the following dax:

GBV
 = VAR year_in_context = SELECTEDVALUE('Date'[Year])
      VAR month_in_focus = MONTH(TODAY())
      VAR day_in_focus = DAY(TODAY())
      VAR last_day1 = DATE(year_in_context, month_in_focus, day_in_focus)  
      VAR last_day2 = EOMONTH(last_day1 ,0)
      VAR first_day = DATE(year_in_context, 1, 1)
      VAR Result =  CALCULATE(
            SUM('bookings'[Price]),
                FILTER(
                ALL('bookings'),
                'bookings'[BookingDate] >= first_day &&
                'bookings'[BookingDate] <= last_day2
                ))
    RETURN
Result

You can check to see if there are filters filtering that are causing the 2024 values to appear incorrectly or to see if the table-to-table relationships are connected correctly.

If the results don't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.