Forum Discussion

icassiem's avatar
icassiem
Post Prodigy
7 months ago
Solved

Line chart data label on min, max, current points only for dynamic hierarchy (sample solution)

Hi,   i posted previously to show data lables on specific points but dynamic for qtr, year, month visual selection here is my prev post, and i received help by trying to create the additional meas...
Need Help
  • icassiem's avatar
    icassiem
    7 months ago

    v-prasare  Thank You

    i have been playing around with chatgpt to help me , the below works on gettinfg the last month total only but not when i roll up to qtr where i want the ucrrent or last qter total to display

    This works for last only that has start of month date
    LastDateValueLabel =  
      VAR MaxFactDate =
        CALCULATE(
            MAX(FACT[Date]),
            ALLSELECTED('Calc_Calendar')
        )
    RETURN
    IF(
        MAX('Calc_Calendar'[Date]) = MaxFactDate,
           FACT[ActualBaseMeasAgg],
        BLANK()
    )


    Now trying date hierarchy:

    VAR CurrentMaxDate =
        CALCULATE(
            MAX(FACT[Date]),
            ALLSELECTED('Calc_Calendar')
        )

    VAR CurrentPeriod =
        SWITCH(
            TRUE(),
            ISINSCOPE('Calc_Calendar'[DateStartOfMonthDescr]), "Month",
            ISINSCOPE('Calc_Calendar'[DateFinQuarterDescr]), "Quarter",
            ISINSCOPE('Calc_Calendar'[DateFinYearDescr]), "Year",
            "Other"
        )

    RETURN
    SWITCH(
        CurrentPeriod,
        "Month",
            CALCULATE(
                FACT[ActualBaseMeasAgg],
                FILTER(
                    ALL('Calc_Calendar'),
                    'Calc_Calendar'[Date] = CurrentMaxDate
                )
            ),
        "Quarter",
            CALCULATE(
                FACT[ActualBaseMeasAgg],
                DATESQTD('Calc_Calendar'[Date])
            ),
        "Year",
            CALCULATE(
                FACT[ActualBaseMeasAgg],
                DATESYTD('Calc_Calendar'[Date])
            ),
        BLANK()
    )

     

v-prasare's avatar
v-prasare
Community Support
7 months ago

Hi icassiem,

To achieve this, we use DAX measures that return values only for the current, minimum, or maximum period within the active date hierarchy context. Power BI automatically displays data labels only for those points where the measure returns a value, while all other points remain unlabeled.
 
Total Sales :=
SUM ( 'Fact Sales'[SalesAmount] )

Last Visible Date :=
MAX ( 'Dim Date'[Date] )
Min Sales :=
MINX (
    VALUES ( 'Dim Date'[Date] ),
    [Total Sales]
)


Max Sales :=
MAXX (
    VALUES ( 'Dim Date'[Date] ),
    [Total Sales]
)





Composite Label :=
VAR _CurrentValue = [Total Sales]
VAR _MinValue = [Min Sales]
VAR _MaxValue = [Max Sales]
VAR _LastDate = [Last Visible Date]

VAR _IsCurrent =
    MAX ( 'Dim Date'[Date] ) = _LastDate

VAR _IsMin =
    _CurrentValue = _MinValue

VAR _IsMax =
    _CurrentValue = _MaxValue

RETURN
SWITCH (
    TRUE (),
    _IsCurrent, _CurrentValue,
    _IsMax,     _CurrentValue,
    _IsMin,     _CurrentValue,
    BLANK ()
)

 

 

 

please try above measure, which should help you resolve the issue. If not please share sample pbix file or sample data in usable format. so, that can try to repro this scenario from our end

 

 

 

 

Thanks,

Prashanth

icassiem's avatar
icassiem
Post Prodigy
7 months ago

v-prasare  thank you so much, i am currently tied with another task but will give this a go soon