Time Intelligence conflict in two measures

Question

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.&nbsp;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] &lt;= MAX('Calendar'[Date])))&nbsp;I also have a another measure to count all failures:Failure# =DISTINCTCOUNT('i0026 PROD_REPAIR'[UNIT_NO_TXT])&nbsp;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(&nbsp;&nbsp;&nbsp; [Failure#],[**bleep** Delivery])&nbsp;The issue I have is when I want to calculate a 3 months rolling average of that MRR%. The measure below don’t work&nbsp;3mra MRR % =CALCULATE(&nbsp;&nbsp;&nbsp; [MRR %],&nbsp;&nbsp;&nbsp; DATESINPERIOD ('Calendar'[Date],LASTDATE('Calendar'[Date]),-3,MONTH)&nbsp;&nbsp;&nbsp; )&nbsp;I think because it DATEINPERIOD filters the measure [**bleep** Delivery] in addition to the original time filter it has:&nbsp;FILTER(ALLSELECTED('Calendar'),'Calendar'[Date] &lt;= MAX('Calendar'[Date])))&nbsp;Please advise.

v-eachen-msft · Answer

Hi&nbsp;bdn008&nbsp;,
&nbsp;
You could try DATESBETWEEN(). Here are the codes for your reference.
&nbsp;
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 ) )
&nbsp;
&nbsp;

bdn008 · Answer

v-eachen-msft&nbsp;Thanks for the reply BUT this did not solve the issue.&nbsp;DATESBETWEEN worked very similarly to DATESINPERIOD.&nbsp;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&nbsp; but I still need to allow both measures to work.&nbsp;&nbsp;Still hoping to get some ideas...

mahoneypat · Answer

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.&nbsp; Doing it at the month level will be less calculation intensive, but you can replace the YearMonth column with Date level, if needed.&nbsp; For example&nbsp;NewMeasure =
VAR __last3months =
    CALCULATETABLE (
        VALUES ( 'Calendar'[YearMonth] ),
        DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
    )
RETURN
    AVERAGEX ( __last3months, DIVIDE ( [Failure#], [**bleep** Delivery] ) )&nbsp;If this works for you, please mark it as the solution.&nbsp; Kudos are appreciated too.&nbsp; Please let me know if not.Regards,Pat&nbsp;

bdn008 · Answer

mahoneypat&nbsp;- 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.&nbsp;&nbsp;I still believe that the issue is the conflict in the time intelligence between the child and parent measure .&nbsp;&nbsp;&nbsp;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)&nbsp;&nbsp;&nbsp;&nbsp;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.&nbsp;&nbsp;For example for Jun-2020 I will expect the correct measure to result in:&nbsp;DIVIDE ((493+484+615), (550027+539328+531332)) =&nbsp;1592/1620687 =&nbsp;0.10%&nbsp;May-2020 - 0.12%Apr-2020 - 0.14% etc.&nbsp;Here is in Table format some of my data I used in this examplemmm-YYYYFailure#QUANTITY**bleep** DeliveryMRR %NewMeasure+NewMeasureJun-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%&nbsp;&nbsp;

mattallington · Answer

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). &nbsp;I recommend expanding the measures including all the implicit CALCULATE functions added automatically inside a measure.&nbsp; &nbsp;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] &lt;= MAX ( 'Calendar'[Date] )
            )
        )
    ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -3, MONTH )
)&nbsp;Then you have to investigate what is is doing, and what you want it to do.&nbsp; I'm not clear what you want, but the above formula shows how the time based filters are tripping over each other.&nbsp;in your post, you show what you expectDIVIDE ((493+484+615), (550027+539328+531332))&nbsp;&nbsp;So you need to write measures that will produce that.&nbsp; 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

Forum Discussion

Time Intelligence conflict in two measures

8 Replies

Related content

update policy and streaming policy conflict

Two conflicting trial lengths visible

Help with Date intelligence

Relationship Conflict

Operation cancelled because of locking conflicts