Forum Discussion

nhlakadee's avatar
nhlakadee
New Member
3 years ago
Solved

Show Min and Max date Values only

Hi, I'm new to Power BI, and I'm trying to show Min Date and Max Date Volumes and the difference between the two in a graph in PowerBI.

I have the following table:

No what I need to do is to show the below graph in PowerBI:

I can't seem to get around this. I just need the Min Date Values and Max Date Values and show the difference as a Reduction in PowerBI.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi nhlakadee ,

     

    Here's my solution.

    1.Create a new calcualted table.

    Table 2 =
UNION ( ROW ( "MonthYear", "Reduction" ), SUMMARIZE ( 'Table', [MonthYear] ) )

     

    2.Create a measure.

    Values = 
VAR _max =
    MAXX ( ALLSELECTED ( 'Table' ), [No_of_Shared_Mailboxes] )
VAR _min =
    MINX ( ALLSELECTED ( 'Table' ), [No_of_Shared_Mailboxes] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table 2'[MonthYear] ),
        CALCULATE (
            MAX ( 'Table'[MonthYear] ),
            FILTER ( ALLSELECTED ( 'Table' ), [No_of_Shared_Mailboxes] = _max )
        ), _max,
        CALCULATE (
            MAX ( 'Table'[MonthYear] ),
            FILTER ( ALLSELECTED ( 'Table' ), [No_of_Shared_Mailboxes] = _min )
        ), _min,
        "Reduction", _min - _max
    )

     

    The result is as follows.

    You could download my attachment for more details.

     

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi nhlakadee ,

     

    Here's my solution.

    1.Create a new calcualted table.

    Table 2 =
UNION ( ROW ( "MonthYear", "Reduction" ), SUMMARIZE ( 'Table', [MonthYear] ) )

     

    2.Create a measure.

    Values = 
VAR _max =
    MAXX ( ALLSELECTED ( 'Table' ), [No_of_Shared_Mailboxes] )
VAR _min =
    MINX ( ALLSELECTED ( 'Table' ), [No_of_Shared_Mailboxes] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table 2'[MonthYear] ),
        CALCULATE (
            MAX ( 'Table'[MonthYear] ),
            FILTER ( ALLSELECTED ( 'Table' ), [No_of_Shared_Mailboxes] = _max )
        ), _max,
        CALCULATE (
            MAX ( 'Table'[MonthYear] ),
            FILTER ( ALLSELECTED ( 'Table' ), [No_of_Shared_Mailboxes] = _min )
        ), _min,
        "Reduction", _min - _max
    )

     

    The result is as follows.

    You could download my attachment for more details.

     

                                                                                                                                                             

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

  • olgad's avatar
    olgad
    Resident Rockstar
    3 years ago

    Hi, here is the min value, the same you do for max, just substitute minx with maxx and you can do simply 'Calendar'[Date] without Year or Month. This way you have max and min displayed. You will have to think how to display

    MinPointAffected =
    VAR MinValueYear = MINX(ALLSELECTED('Calendar'[Date].[Year]), [Measure])
    VAR MinValueMonth = MINX(ALLSELECTED('Calendar'[Date].[Month]), [Measure])
    RETURN
      If(NOT(ISFILTERED('Calendar'[Date].[Month]))&&NOT(ISFILTERED('Calendar'[Date].[Quarter]))&& [Measure] = MinValueYear, [Measure], BLANk())

    The difference is Max-min value, but you have to think how you want to display, you cant do it in one chart, may be some waterfall chart will be good for this purpose.