Calculate CY QTD and Pevious QTD

[Aspiring_Developer]

Hello All ,

I have below data set and I want to calculate Current QTD and previous QTD in a single multi KPI . Here is my data set :-

In my Firt KPI , it should show the QTD for Oct 24 till Dec 24 and in second KPI , it should show the QTD for Aug-24 til sept 24 and it should be dynamic . I am trying below expression but can't do anything :-

Sum(
{<
transaction_dt = {">=$(=Date(MakeDate(If(Month(Today()) >= 4, Year(Today()), Year(Today()) - 1), 4, 1), 'MM/DD/YYYY')) <=$(=Date(Today(), 'MM/DD/YYYY'))"},

[Fiscal Quarter-Year] = {"$(If(Month(Today()) >= 4, Year(Today()), Year(Today()) - 1)) Q" & Ceil(Month(Today()) / 3)},

[Fiscal Year] = {"$(If(Month(Today()) >= 4, Year(Today()), Year(Today()) - 1))"},

[Month-Year] = {">=$(=Date(MakeDate(If(Month(Today()) >= 4, Year(Today()), Year(Today()) - 1), (Ceil(Month(Today()) / 3) - 1) * 3 + 1, 1), 'YYYY-MM-DD')) <=$(=Date(Today(), 'YYYY-MM-DD'))"}
>}
diesel_litres
)

Syntax error :-Error in set  modifier ad hoc element list : ','  or ')' expected .

I am unable to rectify this @Kushal_Chawda 

[Kushal_Chawda]

@Aspiring_Developer  If your fiscal year starts with Month October try below with Month number 10. You can change the month number according to your fiscal year setup

QTD -

Sum(
{<
transaction_dt = {">=$(=QuarterStart(today(),0,10))<=$(=QuarterEnd(today(),0,10))"}
>}
diesel_litres
)

PY QTD-
Sum(
{<
transaction_dt = {">=$(=QuarterStart(addyears(today(),-1),0,10))<=$(=QuarterEnd(addyears(today(),-1),0,10))"}
>}
diesel_litres
)

[Aspiring_Developer]

@Kushal_Chawda  Thank you but I want this to be dynamic . 

[Kushal_Chawda]

@Aspiring_Developer  what do you mean by dynamic?

[Aspiring_Developer]

@Kushal_Chawda   Here The range is set as 10 which is explicitly for the month of October . When the data for January till march 2025 will come , then this KPI should show the data for the latest QTD , i.e for january 2025 . Similarly , previous QTD would become the sum of values starting from Oct 2024 till Dec 2024

[Kushal_Chawda · Accepted solution]

@Aspiring_Developer  My expression is already dynamic. What I meant is that if you're trying to calculate QTD for a fiscal year starting in October, the QuarterStart function requires a parameter to specify that QTD begins in October, not January. That's why the parameter '10' was used. In your case, the following should work

QTD -

Sum(
{<
transaction_dt = {">=$(=QuarterStart(today()))<=$(=QuarterEnd(today()))"}
>}
diesel_litres
)

PY QTD-
Sum(
{<
transaction_dt = {">=$(=QuarterStart(today(),-1))<=$(=QuarterEnd(today(),-1))"}
>}
diesel_litres
)