Hi,
i was asked to calculate QTD 'Target' (Orange line), and present it on a graph.
the QTD calc is based on 2 previous (2022 & 2021 ) timelines.
because this calculation is in our set analysis, it seems we cannot present the outcome on a graph with a timeline.he formula that i'm using is:
if((QuarterStart(today())=max(QuarterDateStart) and GetPossibleCount(Timeline)=1) or (GetSelectedCount(Year)=0 and GetSelectedCount(Timeline)=0 and QuarterStart(today())=max(QuarterDateStart)),
sum(aggr(
(
(
SUM({<QTD={'QTD'},[Sub Stream]={'Systems'},Stream={'Hardware'},QuarterDateStart=$(vMaxDatePY),Year=,Timeline=>}[Sales.Quantity Adjusted])
/
SUM({<[Sub Stream]={'Systems'},Stream={'Hardware'},QuarterDateStart=$(vMaxDatePY),Year=,Timeline=>}[Sales.Quantity Adjusted])
)
+
(
SUM({<QTD={'QTD'},[Sub Stream]={'Systems'},Stream={'Hardware'},QuarterDateStart=$(vMaxDateP2Y),Year=,Timeline=>}[Sales.Quantity Adjusted])
/
SUM({<[Sub Stream]={'Systems'},Stream={'Hardware'},QuarterDateStart=$(vMaxDateP2Y),Year=,Timeline=>}[Sales.Quantity Adjusted])
)
/
2)
* sum({<Stream={'Hardware'}>}[AOP.UNITS])
,QuarterDateStart)),
sum(aggr((
(
(
sum({<[Sub Stream]={'Systems'},Timeline=,Year=,QTD={'QTD'}>}[Sales.Quantity Adjusted])
/
sum({<[Sub Stream]={'Systems'},Timeline=,Year=>}[Sales.Quantity Adjusted])
)
)
),QuarterDateStart))
* sum({<Stream={'Hardware'}>}[AOP.UNITS])
)
as you can see for Q2'23 it does not show any data because the formula for QTD is working on that timeline.
