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

Rolling sum in two different Date fields

Hello,

 

I am trying to get the total hours that were created (Date Field 1) 1 week in the past of the last date and in the next week due date (Date Field 2) 

 

I tried:

New request =
CALCULATE (
[Total Hours],
DATESINPERIOD ( fact_jira[Created], LASTDATE( fact_jira[Created] ) - 1, -7, DAY ) && DATESINPERIOD(fact_jira[Due Date], LASTDATE(fact_jira[Due Date]), 7, DAY)
)
 
DATESINPERIOD ( fact_jira[Created], LASTDATE( fact_jira[Created] ) - 1, -7, DAY )
and
DATESINPERIOD(fact_jira[Due Date], LASTDATE(fact_jira[Due Date]), 7, DAY)
 
works individually, but not with an AND.
 
How can I calculate the Total hours within a range period filtered by rows in two different date fields?
 
Any help would be much appreciated.
 
Thanks!
1 ACCEPTED SOLUTION
Anonymous
Not applicable

@andrejmfreitas 

 

You have to have a proper date table first of all. Secondly, you have to create 2 relationships - one active, one inactive - to the fact table; the active link will be to the date field that is more natural to use (probably [Created]) and the inactive to [Due Date]. Then and only then will you be able to create correct formulas. All columns in fact tables MUST BE HIDDEN. Slicing is allowed only through dimensions. The exception to this is a degenerate dimension.

 

By the way, it's not true at all that these

 

DATESINPERIOD ( fact_jira[Created], LASTDATE( fact_jira[Created] ) - 1, -7, DAY )
DATESINPERIOD(fact_jira[Due Date], LASTDATE(fact_jira[Due Date]), 7, DAY)

 

work correctly. This is just a coincidence and will be wrong before you know it. The explanation why this is so can be found in the documentation I gave you a link to.

 

Here's the formula you need when you've got just one Dates table:

 

 

[New Request] =
// Remember: you must never slice and dice
// by any fields that exist in a fact table.
// A fact table must only contain keys to
// dimensions and figures/measures at the higest
// granularity. The only exception to this rule
// is degenerate dimensions but you don't seem
// to have them. Best practice is to totally
// hide the fact table from the user in the UI.
// If you don't do it this way, the user will be happy
// to break your reports and calculations without
// even knowing about it.
var vLatestVisibleDay = MAX( Dates[Date] )
var vOneWeekInPast = 
    DATESINPERIOD(
        // Dates must be marked as a date
        // table in the model for this to
        // work.
        Dates[Date],
        vLatestVisibleDay - 1,
        -7,
        DAY
    )
var vOneWeekInFuture =
    DATESINPERIOD(
        Dates[Date],
        vLatestVisibleDay,
        7,
        DAY
    )
RETURN
CALCULATE(
    [Total Hours],
    // Because the Date table has 2 relationships
    // to the fact table, one can't use 2 different
    // filters coming from one table in the same
    // formula. Hence, you have to use TREATAS.
    TREATAS(
        CROSSJOIN(
            vOneWeekInPast,
            vOneWeekInFuture
        ),
        // Remember that no columns from the fact
        // table must be exposed to the end user.
        // If you let them put filters on such columns,
        // this formula may not work correctly. But
        // such a practice goes against the rules of
        // a good dimensional design and you'll have
        // yourself to blame if you do it.
        fact_jira[Created],
        fact_jira[Due Date]
    ),
    REMOVEFILTERS( Dates )    
)

 

 

But you can also have a different design. You can have 2 dates tables, one for Created and one for Due Date. Let's call them 'Created Dates' and 'Due Dates.' These will be connected to your fact table on the respective fields, e.g., 'Created Dates'[Date] 1 -> * fact_jira[Created]. Then our measure will look different:

 

 

[New Request] =
var vLatestVisibleDay = MAX( Dates[Date] )
var vOneWeekInPast = 
    DATESINPERIOD(
        // 'Created Dates' must be marked
        // as a date table in the model.
        'Created Dates'[Date],
        vLatestVisibleDay - 1,
        -7,
        DAY
    )
var vOneWeekInFuture =
    DATESINPERIOD(
        // 'Due Dates' must be marked
        // as a date table in the model.
        'Due Dates'[Date],
        vLatestVisibleDay,
        7,
        DAY
    )
RETURN
CALCULATE(
    [Total Hours],
    vOneWeekInPast,
    vOneWeekInFuture
)

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Of course it didn't work. Time-intel functions DO NOT WORK on tables that aren't proper date tables. Please go to this page dax.guide/datesinperiod and read the documentation in full.

Thanks for your feedback Daxer. It's clear the way I am doing does not work, but what does work?

 

In the documentation sent, I couldn't find any example with two different date fields as a range. Do you have any tips?

 

Thank you very much for your help in advance.

 

 

Anonymous
Not applicable

@andrejmfreitas 

 

You have to have a proper date table first of all. Secondly, you have to create 2 relationships - one active, one inactive - to the fact table; the active link will be to the date field that is more natural to use (probably [Created]) and the inactive to [Due Date]. Then and only then will you be able to create correct formulas. All columns in fact tables MUST BE HIDDEN. Slicing is allowed only through dimensions. The exception to this is a degenerate dimension.

 

By the way, it's not true at all that these

 

DATESINPERIOD ( fact_jira[Created], LASTDATE( fact_jira[Created] ) - 1, -7, DAY )
DATESINPERIOD(fact_jira[Due Date], LASTDATE(fact_jira[Due Date]), 7, DAY)

 

work correctly. This is just a coincidence and will be wrong before you know it. The explanation why this is so can be found in the documentation I gave you a link to.

 

Here's the formula you need when you've got just one Dates table:

 

 

[New Request] =
// Remember: you must never slice and dice
// by any fields that exist in a fact table.
// A fact table must only contain keys to
// dimensions and figures/measures at the higest
// granularity. The only exception to this rule
// is degenerate dimensions but you don't seem
// to have them. Best practice is to totally
// hide the fact table from the user in the UI.
// If you don't do it this way, the user will be happy
// to break your reports and calculations without
// even knowing about it.
var vLatestVisibleDay = MAX( Dates[Date] )
var vOneWeekInPast = 
    DATESINPERIOD(
        // Dates must be marked as a date
        // table in the model for this to
        // work.
        Dates[Date],
        vLatestVisibleDay - 1,
        -7,
        DAY
    )
var vOneWeekInFuture =
    DATESINPERIOD(
        Dates[Date],
        vLatestVisibleDay,
        7,
        DAY
    )
RETURN
CALCULATE(
    [Total Hours],
    // Because the Date table has 2 relationships
    // to the fact table, one can't use 2 different
    // filters coming from one table in the same
    // formula. Hence, you have to use TREATAS.
    TREATAS(
        CROSSJOIN(
            vOneWeekInPast,
            vOneWeekInFuture
        ),
        // Remember that no columns from the fact
        // table must be exposed to the end user.
        // If you let them put filters on such columns,
        // this formula may not work correctly. But
        // such a practice goes against the rules of
        // a good dimensional design and you'll have
        // yourself to blame if you do it.
        fact_jira[Created],
        fact_jira[Due Date]
    ),
    REMOVEFILTERS( Dates )    
)

 

 

But you can also have a different design. You can have 2 dates tables, one for Created and one for Due Date. Let's call them 'Created Dates' and 'Due Dates.' These will be connected to your fact table on the respective fields, e.g., 'Created Dates'[Date] 1 -> * fact_jira[Created]. Then our measure will look different:

 

 

[New Request] =
var vLatestVisibleDay = MAX( Dates[Date] )
var vOneWeekInPast = 
    DATESINPERIOD(
        // 'Created Dates' must be marked
        // as a date table in the model.
        'Created Dates'[Date],
        vLatestVisibleDay - 1,
        -7,
        DAY
    )
var vOneWeekInFuture =
    DATESINPERIOD(
        // 'Due Dates' must be marked
        // as a date table in the model.
        'Due Dates'[Date],
        vLatestVisibleDay,
        7,
        DAY
    )
RETURN
CALCULATE(
    [Total Hours],
    vOneWeekInPast,
    vOneWeekInFuture
)

 

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