Forum Discussion
Line chart data label on min, max, current points only for dynamic hierarchy (sample solution)
- 6 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 displayThis 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 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()
)