cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Time Intelligence conflict in two measures

Hi,

I have a report that uses a Measure to calculate a cumulative number based on time. I have a calendar tables shared across all tables. My issue is that a measure with time intelligence (TI) using another measure with a different time Intelligence will be confused. I need help to find a way to allow the original TI to not be filtered out by the using measure.

 

The Cumulative measure is blow and it works just fine.

**bleep** Delivery = CALCULATE(

SUM('i0026 AUX_MAT_DLV_PRD'[QUANTITY]),

FILTER(ALLSELECTED('Calendar'),

'Calendar'[Date] <= MAX('Calendar'[Date])))

 

I also have a another measure to count all failures:

Failure# =

DISTINCTCOUNT('i0026 PROD_REPAIR'[UNIT_NO_TXT])

 

I need to calculate a Monthly rate (which is failure# divided by **bleep** Delivery) and the measure below works perfectly per month in a time table.

MRR % =

DIVIDE(

    [Failure#],[**bleep** Delivery])

 

The issue I have is when I want to calculate a 3 months rolling average of that MRR%. The measure below don’t work

 

3mra MRR % =

CALCULATE(

    [MRR %],

    DATESINPERIOD ('Calendar'[Date],LASTDATE('Calendar'[Date]),-3,MONTH)

    )

 

I think because it DATEINPERIOD filters the measure [**bleep** Delivery] in addition to the original time filter it has:

 

FILTER(ALLSELECTED('Calendar'),

'Calendar'[Date] <= MAX('Calendar'[Date])))

 

Please advise.

8 REPLIES 8
Highlighted
Community Support
Community Support

Re: Time Intelligence conflict in two measures

Hi @bdn008 ,

 

You could try DATESBETWEEN(). Here are the codes for your reference.

 

Measure 2 =
VAR __EndDate =
    EOMONTH ( LASTDATE ( 'Table'[Date] ), 0 )
VAR __StartDate =
    DATE ( YEAR ( __EndDate ), MONTH ( __EndDate ) - 3, 1 )
RETURN
    CALCULATE ( [Measure], DATESBETWEEN ( 'Table'[Date], __StartDate, __EndDate ) )

 

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Helper I
Helper I

Re: Time Intelligence conflict in two measures

@v-eachen-msft Thanks for the reply BUT this did not solve the issue. 

DATESBETWEEN worked very similarly to DATESINPERIOD.

 

The Issue is how to allow different time filtering on the second measure WHILE maitaining the original time filtering on the first Measure. IfI had a calculated table that will result in a value in the fact table, maybe it would work. But I really prefer to use a measure. Also, I kno the ALLSELECT in the first measure is the issue  but I still need to allow both measures to work. 

 

Still hoping to get some ideas...

Highlighted
Community Champion
Community Champion

Re: Time Intelligence conflict in two measures

You should be able to make a virtual table of your last 3 months (or the days in the last 3 months) and use AVERAGEX over that table to get your desired result.  Doing it at the month level will be less calculation intensive, but you can replace the YearMonth column with Date level, if needed.  For example

 

NewMeasure =
VAR __last3months =
    CALCULATETABLE (
        VALUES ( 'Calendar'[YearMonth] ),
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
    )
RETURN
    AVERAGEX ( __last3months, DIVIDE ( [Failure#], [**bleep** Delivery] ) )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

Highlighted
Helper I
Helper I

Re: Time Intelligence conflict in two measures

@mahoneypat - Thanks but while this gets me closer I am not there yet.

Not that it solved the issue but I have modified your measure to calculate the 3month average and not the average of the percentages. 

 

I still believe that the issue is the conflict in the time intelligence between the child and parent measure .

 

 

 

NewMeasure+ = 
VAR __last3months =
    CALCULATETABLE (
        VALUES ( 'Calendar'[Date] ),
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
    )
VAR _3M_FAILURES =
    SUMX ( __last3months, [Failure#])
VAR _3MCUM_Del =
    SUMX ( __last3months, [**bleep** Delivery])
RETURN
    DIVIDE (_3M_FAILURES,_3MCUM_Del)

 

 

 

 

Here is what I get with the original NewMeasure and NewMeasure+ - both wrong and the **bleep** Delivery used, is not the SUM of the last 3 months as the **bleep** measure will try to filter. In this table it looks like a number but in PBI it is a measure that changes when applying the filter with the parent measure. 

 

For example for Jun-2020 I will expect the correct measure to result in: 

DIVIDE ((493+484+615), (550027+539328+531332)) = 

1592/1620687 = 0.10%

 

May-2020 - 0.12%

Apr-2020 - 0.14% etc.

 

Here is in Table format some of my data I used in this example

mmm-YYYYFailure#QUANTITY**bleep** DeliveryMRR %NewMeasure+NewMeasure
Jun-2020493106995500270.09%0.12%0.23%
May-202048479965393280.09%0.16%0.25%
Apr-202061555495313320.12%0.16%0.20%
Mar-202076164415257830.14%0.15%0.18%
Feb-202078590135193420.15%0.13%0.17%
Jan-202044967075103290.09%0.11%0.15%
Dec-201975295595036220.15%0.10%0.13%
Nov-201956489844940630.11%0.10%0.16%
Oct-2019670130624850790.14%0.07%0.10%

 

 

Highlighted

eRe: Time Intelligence conflict in two measures

I recommend using extreme caution when nesting time intelligence meausres - I rarely do it. Any function using CALCULATE is manipulating filter context, and in your case you have an outer filter context and an inner filter context (inside the embedded measures).  I recommend expanding the measures including all the implicit CALCULATE functions added automatically inside a measure.   When I did this, I got the following:

3mra MRR % =
CALCULATE (
    DIVIDE (
        [Failure#],
        CALCULATE (
            SUM ( 'i0026 AUX_MAT_DLV_PRD'[QUANTITY] ),
            FILTER (
                ALLSELECTED ( 'Calendar' ),
                'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            )
        )
    ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
)

 Then you have to investigate what is is doing, and what you want it to do.  I'm not clear what you want, but the above formula shows how the time based filters are tripping over each other.

 

in your post, you show what you expect

DIVIDE ((493+484+615), (550027+539328+531332)) 

 

So you need to write measures that will produce that.  It would seem to be something like this

=
DIVIDE (
    SUMX (
        CALCULATETABLE (
            VALUES ( calendar[mmm-YYYY] ),
            DATESINPERIOD ( calendar[date], MAX ( calendar[date] ), -3, MONTH )
        ),
        [Failure #]
    ),
    SUMX (
        CALCULATETABLE (
            VALUES ( calendar[mmm-yyyy] ),
            DATESINPERIOD ( calendar[date], MAX ( calendar[date] ), -3, MONTH )
        ),
        [Cuum Delivery]
    )
)

There is probably a more efficent way to write it, I guess



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Helper I
Helper I

Re: eRe: Time Intelligence conflict in two measures

Thanks @MattAllington ,

I appreciate it but unfortunately this did not resolve my issue. 

 

I understand your caution and really know that the nested inner filter can not live with the outer filter, which is the source of my issue. And Your suggestion resulted in the same result as my  NewMeasure+ measure.

 

There are two dynamic time lines using time intelligence measures that contradict each other.

One is all times before current (filtered time on a time line to have cumulative values)

And the other is just the last 3 months to calculate a 3-month-running-average.

 

My issue is that as the first time line is not just values but rather a dynamic time intelligence measure, when I apply the second measure using the first, it’ll crop the first time line and will change the values.

 

Here is what I need – if I could “Lock” into a table the **bleep** values as they are [**bleep** Delivery], then I could apply your measure and it should work – it works in Excel.

 

Still a quandary.

Highlighted

Re: eRe: Time Intelligence conflict in two measures

These things are very difficult to solve "conceptually" without the model. If you send me the workbook (you have my email already), and make sure you include a clear explanation of the problem, the current result and the expected result, then I will take a look.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Helper I
Helper I

Re: eRe: Time Intelligence conflict in two measures

Thanks @MattAllington,

I have sent you an email with attachments. The excel with the data tabs as well as the PBIX file. I have spend some time cleaning the data from any business sensitive information but all the required fields for the calculation are there, as well as the relationships between the tables and a common calendar table.  .

 

I appreciate you taking the time.

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors