Time Intelligence conflict in two measures

[bdn008]

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.

[v-eachen-msft]

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

[bdn008]

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

[mahoneypat]

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

[bdn008]

@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-YYYY	Failure#	QUANTITY	**bleep** Delivery	MRR %	NewMeasure+	NewMeasure
Jun-2020	493	10699	550027	0.09%	0.12%	0.23%
May-2020	484	7996	539328	0.09%	0.16%	0.25%
Apr-2020	615	5549	531332	0.12%	0.16%	0.20%
Mar-2020	761	6441	525783	0.14%	0.15%	0.18%
Feb-2020	785	9013	519342	0.15%	0.13%	0.17%
Jan-2020	449	6707	510329	0.09%	0.11%	0.15%
Dec-2019	752	9559	503622	0.15%	0.10%	0.13%
Nov-2019	564	8984	494063	0.11%	0.10%	0.16%
Oct-2019	670	13062	485079	0.14%	0.07%	0.10%

[MattAllington]

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

[bdn008]

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.

[MattAllington]

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.

[bdn008]

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.