Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CatManKuhn
Helper II
Helper II

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
Anonymous
Not applicable

@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
Anonymous
Not applicable

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.

Anonymous
Not applicable

@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

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 @Anonymous!

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. 

Anonymous
Not applicable

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.

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.

Anonymous
Not applicable

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

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

 

Anonymous
Not applicable

@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
Not applicable

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors