Forum Discussion

schae235's avatar
schae235
New Member
1 year ago
Solved

Filter on a Measure Applied to another Measure

I am trying to gather data for the last production work day. To do this I have a field in the date table called WorkDay.  I am have a measure (ReportingDate) that calculates the max date in the date table where workday is true and it's less than today, my formula is below.  For reference 'Date' is the date table and the field [Date] is the short format date

 

ReportinDate =
CALCULATE(
            MAX('Date'[Date])
            ,FILTER( 'Date', 'Date'[Date] < TODAY())
            ,FILTER('Date','Date'[Workday] = TRUE)))
 
This gives me exactly what I want with a date of 2/3/2025 (The current date at time of writing is 2/4/2025).  I also verified yesterday, a monday, it gave me the date of 1/31/2025. My issue comes in with other date field that I am trying to calculate based off the ReportingDate.  For example I am trying to get the MinMonthDate (essentially the first day of the month) and using the following formula:
 
MinMonthDate =
CALCULATE(
    MIN('Date'[Date])
    ,FILTER('Date','Date'[Year] = YEAR([ReportingDate]))
    ,FILTER('Date','Date'[MonthNumber] = MONTH([ReportingDate]))
    )
 
And I get a result of 1/3/2022.  When I hard code the year and month in the MinMonthDate expression I get the result I expect (2/1/2025).  I verified that the YEAR(ReportingDate) is 2025 and MONTH(ReportingDate) is 2 because I made a different measure to check them. 
 
I beleive what is happening is that the filters I applied to the Reporting Date are carrying through to the MinMonthDate.  My Date Table starts at 1/1/2022 but the first time Workday is true is 1/3/2022.

 

   
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    The problem is in how you are specifying the filters. By using FILTER over the entire date table you are calculating [Reporting Date] for every row of the date table. Due to context transition, the row context generated by the iteration is translated into a filter context which then affects the calculation of [Reporting Date].

    You need to calculate [Reporting Date] only once, in the current filter context, and then apply that as a filter.

    MinMonthDate =
VAR ReportingDate = [ReportingDate]
VAR Result =
    CALCULATE (
        MIN ( 'Date'[Date] ),
        'Date'[Year] = YEAR ( ReportingDate ),
        'Date'[MonthNumber] = MONTH ( ReportingDate )
    )
RETURN
    Result

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    I find it useful to have a start of month column in the date table. You could create a calculated column in DAX like

    Start of month =
EOMONTH ( 'Date'[Date], -1 ) + 1

    You could then write the MinMonthDate measure like

    MinMonthDate =
CALCULATE (
    MAX ( 'Date'[Start of month] ),
    'Date'[Date] < TODAY (),
    'Date'[Workday] = TRUE
)
  • schae235's avatar
    schae235
    New Member
    1 year ago

    johnt75 thank you for your reply and I believe that could be a solution for the first of the month.  I was hoping for a reason why the measure is showing the behavior it is.  I have other measures that calcualte off that one other than just he first of the month.  For example I also have the first of the week, etc.  I guess a work around would be to add all the different values to the table but I just see that is ineffective. It would be nice to have it calculated.

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    The problem is in how you are specifying the filters. By using FILTER over the entire date table you are calculating [Reporting Date] for every row of the date table. Due to context transition, the row context generated by the iteration is translated into a filter context which then affects the calculation of [Reporting Date].

    You need to calculate [Reporting Date] only once, in the current filter context, and then apply that as a filter.

    MinMonthDate =
VAR ReportingDate = [ReportingDate]
VAR Result =
    CALCULATE (
        MIN ( 'Date'[Date] ),
        'Date'[Year] = YEAR ( ReportingDate ),
        'Date'[MonthNumber] = MONTH ( ReportingDate )
    )
RETURN
    Result
    • schae235's avatar
      schae235
      New Member
      1 year ago

      Thank you. I wish that didn't mean that I need to redo all the measures that are dependent on the reporting date but at least I have a path forward.