Forum Discussion

CatManKuhn's avatar
CatManKuhn
Helper II
5 years ago
Solved

Remove Future Dates from Rolling 12 Month Measures

Hi all, 

 

Hoping someone can help and that I am not duplicating a previous post. I am working on revising many measures in my model to account for a future dated date table. I am currently stuck on Rolling 12 Month Hiring Sum. Here is the DAX:

 

Rolling 12 Month External Hiring Sum = 
CALCULATE (
    COUNTROWS ( 'External Hires' ),
    CALCULATETABLE (
    DATESBETWEEN (
        'Date'[Date],
         NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ),
        LASTDATE ( 'Date'[Date] )
    ), 'Date'[DatesWithHires] = TRUE ))

 

I created a calculated column on the Date table called DatesWithHires. This flags dates that are on or before the max hire date which would never be in the future. When I try to apply this using CALCULATETABLE there is no change. If I filter the visual itself by DatesWithHires = TRUE, then the visual works as expected.

 

Here is a screenshot of the outcome from my measure:

Here is a screenshot of what I am expecting. I can acheive this using a visual filter with DatesWithHires = True. I really need this in the measure though.

 

 

Any guidance is much appreciated. Thanks in advance!

DAX
Future Dates
Measures
  • Anonymous's avatar
    Anonymous
    5 years ago

    CatManKuhn 

     

    My measure absolutely works the way you want. It's an adaptation of the technique from www.sqlbi.com by Alberto and Marco. It uses a technique known as "the interception of filters" to do the right thing. I have used it in many other projects and it's always worked correctly. I would really be surprised if it didn't do what it's supposed to. It surely does.

     

    And, of course, it's not true what you say: "I dot not have that issue with the 27 until I use your measure." If you take a look at your very first post in this thread... you'll notice that, indeed, you also have a blank row paired with the numer 26,438. It means the blank row either does exist in your data (it might be a blank or an empty text ""), or the model is creating it due to RI problems. There is NO other possibility.

     

    By the way, I'm talking about this measure (just to be sure we're talking about the same thing):

    Rolling 12 Month External Hiring Sum =
var VeryLastDateWithAnyHires =
    CALCULATE(
        MAX( 'Date'[Date] ),
        'Date'[DatesWithHires],
        ALL( 'Date' )
    )
var TotalPeriodWithHires =
    CALCULATETABLE(
        DISTINCT( 'Date'[Date] ),
        'Date'[Date] <= VeryLastDateWithAnyHires
    )
var EffectiveDates =
    INTERSECT(
        TotalPeriodWithHires,
        DISTINCT( 'Date'[Date] )
    )
var MaxEffectiveDate =
    MAXX( EffectiveDates, 'Date'[Date] )
var Result =
    CALCULATE(
        COUNTROWS( 'External Hires' ),
        DATESINPERIOD(
            'Date'[Date],
            MaxEffectiveDate,
            -1,
            YEAR
        ),
        KEEPFILTERS( 'Date'[DatesWithHires] )
    )
return
    Result

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    Rolling 12 Month External Hiring Sum =
CALCULATE(
    COUNTROWS( 'External Hires' ),
    DATESINPERIOD(
        'Date'[Date],
        MAX( 'Date'[Date] ),
        -1,
        YEAR
    ),
    // DatesWithHires is a logical column
    // so there's no need to equate it to
    // TRUE.
    KEEPFILTERS( 'Date'[DatesWithHires] )
)
    • CatManKuhn's avatar
      CatManKuhn
      Helper II
      5 years ago

      Thanks! That is super, super close. I am noticing future months and a count of 27 hires that are not attributed to a date. I can't quite figure out why. 

       

       

      This is what I get when using the DatesWithHires filter applied on the visual. This is what I would expect the measure to return.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        CatManKuhn 

         

        The fact that you see BLANK in the visual means that you've got referential integrity problems in your data. You should fix it. Secondly, it's pretty obvious why you've got numbers running all the way through all the months, even into the future. The fact that the measure filters by DatesWithHires does not mean that in the future the formula can't see any dates. In fact it does see dates in the past throughout all the year before the current MAX date. To do what you want, the formula must be different, of course. It must check if in the currently visible period there any dates where DatesWithHires is true and then calculate as above. If there are no such dates, BLANK should be returned. Can you program this yourself?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello CatManKuhn 
    Try this.
    Create a calculated column in the date table.
    IsFuture =
    IF(Dates[Date] > Today(), "Yes", "No")
    Apply visual level filter to No.

    • CatManKuhn's avatar
      CatManKuhn
      Helper II
      5 years ago

      Thanks TarunSharma. This method will absolutely work, but I want to utilize this in the measure itself so that I don't have to have users add a visual filter everytime they use the measure.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        CatManKuhn 

         

        My measure absolutely works the way you want. It's an adaptation of the technique from www.sqlbi.com by Alberto and Marco. It uses a technique known as "the interception of filters" to do the right thing. I have used it in many other projects and it's always worked correctly. I would really be surprised if it didn't do what it's supposed to. It surely does.

         

        And, of course, it's not true what you say: "I dot not have that issue with the 27 until I use your measure." If you take a look at your very first post in this thread... you'll notice that, indeed, you also have a blank row paired with the numer 26,438. It means the blank row either does exist in your data (it might be a blank or an empty text ""), or the model is creating it due to RI problems. There is NO other possibility.

         

        By the way, I'm talking about this measure (just to be sure we're talking about the same thing):

        Rolling 12 Month External Hiring Sum =
var VeryLastDateWithAnyHires =
    CALCULATE(
        MAX( 'Date'[Date] ),
        'Date'[DatesWithHires],
        ALL( 'Date' )
    )
var TotalPeriodWithHires =
    CALCULATETABLE(
        DISTINCT( 'Date'[Date] ),
        'Date'[Date] <= VeryLastDateWithAnyHires
    )
var EffectiveDates =
    INTERSECT(
        TotalPeriodWithHires,
        DISTINCT( 'Date'[Date] )
    )
var MaxEffectiveDate =
    MAXX( EffectiveDates, 'Date'[Date] )
var Result =
    CALCULATE(
        COUNTROWS( 'External Hires' ),
        DATESINPERIOD(
            'Date'[Date],
            MaxEffectiveDate,
            -1,
            YEAR
        ),
        KEEPFILTERS( 'Date'[DatesWithHires] )
    )
return
    Result
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    The blank row is added by the model if there are dates in a fact table that do not have counterparts in the date table. Secondly, I have given you a measure above.

    • CatManKuhn's avatar
      CatManKuhn
      Helper II
      5 years ago

      Thanks daxer, but again there are no dates in my hires data that are missing on my date table. I dot not have that issue with the 27 until I use your measure. Your measure doesn't quite accomplish what I am looking for, but I appreciate the help.