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 very much

  • 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

  • icassiem's avatar
    icassiem
    Post Prodigy
    7 months ago

    v-prasare  hi, i have been battling, i cant share any screenshots or mock fata but with my date calendar on day my total, min & max are the same total which i think is expetant with only v;alue per month but the lastvisible date is every start of month day for the year,  it displaying at every day and not the last date within the date range on year, kindly adviise?