Display only Max and Min values for line graph per month by fiscal year

[ACraig08]

I am wondering if it is possible to only show the maximum and minimum values within each month as highlighted.

[Alex87]

Hello,

Yes, I think it is possible. If you need a more detailed answer than Yes/No, please provide sample data so that we can replicate your use case.

[ACraig08]

Yes I do need more than a Yes/No, I would like help on how to achieve this. However, I cannot provide sample data as it is private. I can say that it looks like this:

Unique ID	Date	Fiscal Year	Amount
a	05/02/2021	2021	23,153,152.00
b	06/22/2021	2021	135,258.00
c	08/24/2021	2022	458,158.00
d	01/01/2022	2022	12,252,255.00

I created a measure for the average amount: sum(Amount)/distinctcount(Unique ID) + 0

I have the "+ 0" so that my line chart will show all the lines from start to finish.

X-axis = Date: Month

Y-Axis = Average Amount

Legend = Fiscal Year

I hope that helps

[v-xinruzhu-msft · Accepted solution]

Hi,

@Alex87 , thanks for the concern about the problem, and i want to offer somre more information for user to refer to.

hello @ACraig08  based on your sample data, i create some sample data

Then  and your average amout need to change to the following.

Average amount =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( 'Data'[Unqiue ID] ),
        ALLEXCEPT ( 'Data', 'Data'[Date].[MonthNo], 'Data'[Years] )
    )
VAR b =
    CALCULATE (
        SUM ( 'Data'[Amount] ),
        ALLEXCEPT ( 'Data', 'Data'[Date].[MonthNo], 'Data'[Years] )
    )
RETURN
    DIVIDE ( b, a ) + 0

Then create a data label measure

Data_label =
VAR a =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Data' ),
            [Date].[MonthNo] IN VALUES ( 'Data'[Date].[MonthNo] )
        ),
        [Average amount]
    )
VAR b =
    MINX (
        FILTER (
            ALLSELECTED ( 'Data' ),
            [Date].[MonthNo] IN VALUES ( 'Data'[Date].[MonthNo] )
        ),
        [Average amount]
    )
RETURN
    IF ( [Average amount] IN { a, b }, [Average amount] )

Then put the following field to line visual.

Then open data label and put the data label measure to the value.

Output

Best Regards!

Yolo Zhu

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

[ACraig08]

That worked perfectly!! Thank you!