Forum Discussion

Preetish_1's avatar
Preetish_1
Helper I
9 years ago
Solved

Dynamic Measures based on filtering

 

 

 

Hello everyone,

The red target line in the above visual is calculated using the following Measure.

 

Target =

var
FirstDt = FIRSTDATE(T1[C_FullDate].[Date])

var Targ = CALCULATE(
     DIVIDE( SUM(T1[Target %]); COUNTROWS(T1));
     DATESINPERIOD( T1[C_FullDate].[Date]; FirstDt; 12 ; MONTH))

RETURN
Targ

 

1. Is there a way to calculate this target for a period of 6/12 months, and apply it across the entire filtered range? (In the above visual its all 95% as the targets are identical, howerver when they are not equal, the target line varies over the different months, so I would like to be able to calculate a value based on the first or last month, and use the same taget across the entire visual)

 

2. I would also like to know, if there is a way to not hard code 6/12 months into the measure, and if it is possible to make the interval a variable? For example, if we filter the visual for a 9 month range, the target should be calculated for a time period of 9 months and the same target is to be applied for all the 9 months.

 

Any help would be greatly appreciated, as i have been on this for a while and I am unable to figure it out.
Thanks.

  • Hi Preetish_1,

     

    If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

    Target =
    CALCULATE (
        DIVIDE ( SUM ( T1[Target %] ); COUNTROWS ( T1 ) );
        ALLSELECTED ( T1[C_FullDate] )
    )

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee

    Hi Preetish_1,

     

    If I understand you correctly, the formula below should work in your scenario. :smileyhappy:

    Target =
    CALCULATE (
        DIVIDE ( SUM ( T1[Target %] ); COUNTROWS ( T1 ) );
        ALLSELECTED ( T1[C_FullDate] )
    )

     

    Regards