cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CatManKuhn
Frequent Visitor

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:

CatManKuhn_0-1617807312459.png

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.

 

CatManKuhn_1-1617807364558.png

 

Any guidance is much appreciated. Thanks in advance!

1 ACCEPTED SOLUTION

@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

View solution in original post

13 REPLIES 13
TarunSharma
Responsive Resident
Responsive Resident

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.

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.

@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

View solution in original post

CatManKuhn
Frequent Visitor

I extended my calendar to 1/1/2015 and it solved my issue. Not exactly sure why because 1/1/2016 is on my date table and on the external hires table. Regardless thank you so much for your help and expertise. Time to validate and dissect your measure. Thanks again @daxer!

CatManKuhn
Frequent Visitor

When I say I do not get that issue I am looking at just my basic count of hires. There is a date associated for each record and each date exists on the date table. I do not understand why this measure would suddenly show 27 that are not associated with a date. If each record has a date that exists on the date table, why would a rolling 12 month show 27 null dates?

Looks like it associated with 1/1/2016. 

daxer
Solution Sage
Solution Sage

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

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.

daxer
Solution Sage
Solution Sage

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

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. 

 

CatManKuhn_0-1617816337870.png

 

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

 

CatManKuhn_1-1617816419721.png

 

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

daxer
Solution Sage
Solution Sage

Here's the measure:

 

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

 

However, whether it does what you want in the first year or not... depends on what your requirements are. For instance, is it correct to say that the 12-month sum is X if you only have 5 months of data? This happens in you calculate this measure in May of the first year in your Date table...

 

CatManKuhn
Frequent Visitor

Thanks daxer. I'm not sure what the referential integrity issue would be. I have checked the hires source and I do not have any null dates. All dates exist on the Date table, so I am unsure why this would return 27 rows without a date associated with it. 

 

Here is a simple count of the external hires:

 

CatManKuhn_1-1617823060868.png

 

I want the Rolling 12 Month External Hires Sum measure to sum the last 12 months of hires. For example, 2020 row would should the past year of hires (1/1/2020 - 12/31/2020). For April 2021, this would calculate 4/8/2020 - 4/7/2021. I have successfully done so when my Date table has an end date equal to today, but trying to add future dates is making this challenging for me. I appreciate your help.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors