Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Plot Line Chart from 2 independent date slicer

Hello Power BI community,   Hope everyone is safe and doing good.   I need help in regards to a chart layout requirement . I have 2 date slicers Slicer_Date1 and Slicer_Date2   I need to show...
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Two new date tables and measures created as below:

    min =
CALCULATE (
    MIN ( 'Table 2'[Index] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Date] >= MIN ( date1[Date] )
            && 'Table 2'[Date] <= MAX ( date1[Date] )
    )
)


max =
CALCULATE (
    MAX ( 'Table 2'[Index] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Date] >= MIN ( date1[Date] )
            && 'Table 2'[Date] <= MAX ( date1[Date] )
    )
)


average =
CALCULATE (
    AVERAGE ( 'Table 2'[Index] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Date] >= MIN ( date1[Date] )
            && 'Table 2'[Date] <= MAX ( date1[Date] )
    )
)


countall =
COUNT ( 'Table 2'[id] )

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous 

Two new date tables and measures created as below:

min =
CALCULATE (
    MIN ( 'Table 2'[Index] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Date] >= MIN ( date1[Date] )
            && 'Table 2'[Date] <= MAX ( date1[Date] )
    )
)


max =
CALCULATE (
    MAX ( 'Table 2'[Index] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Date] >= MIN ( date1[Date] )
            && 'Table 2'[Date] <= MAX ( date1[Date] )
    )
)


average =
CALCULATE (
    AVERAGE ( 'Table 2'[Index] ),
    FILTER (
        ALL ( 'Table 2' ),
        'Table 2'[Date] >= MIN ( date1[Date] )
            && 'Table 2'[Date] <= MAX ( date1[Date] )
    )
)


countall =
COUNT ( 'Table 2'[id] )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hey v-juanli-msft ,

 

How would you implement a Lower Control Limit instead of MIN for these formulas? I have tried to change this min to a measure I have which is basically doing this:

 

Lower Control Limit formula is AVERAGE(VALUE) - STD.DEV(VALUE)*3
 
I tried to create the formula replacing minimum but unable to get it to work. It is giving me same value as average.