Current FYQ

[alexshaji]

HI Members,

I have quite a few places in the Dashboard where I need to display the current and previous quarter results. My project follows the FYQ as:

Nov - Jan = Q1
Feb - Apr=Q2
May - Jul=Q3
Aug - Oct=Q4

The table has the following:

Cust ID	SalesFYQ	SalesDate	Business Unit	Sales Amount
121	FY16 Q2	24-03-2016	ABC	12000
123	FY17 Q1	16-12-2017	AAV	2000
142	FY20 Q1	21-11-2020	GBC	500
116	FY20 Q2	...	AAA	6897
1220	FY20 Q3	...	....	7789
136	FY20 Q4	...	...	69369
400	FY21 Q1	...	....	421
422	FY22 Q2	...	...	1589

Now Ideally every Quarter the display will need to change. FIr examples this quarter

Current Qtr Sales                               Previous Qtr Sales

FY20 Q4 = 69369                                 FY20 Q3 = 7789

How can I achieve this? Please help me

[alexshaji]

Guys

[Kushal_Chawda]

Create variables like below

vCurrentQuarterStart = date(quarterstart(max(SalesDate),0,11))

vCurrentQuarterEnd = date(quarterend(max(SalesDate),0,11))

vPreviousQuarterStart = date(quarterstart(max(SalesDate),-1,11))

vPreviousQuarterEnd = date(quarterend(max(SalesDate),-1,11))

Now you can use below expression to get the sales amount

Sum({<Quarter, Month,Week, SalesDate,SalesDate={">=$(vCurrentQuarterStart)<=$(vCurrentQuarterEnd)"}>}SalesAmount)

Sum({<Quarter, Month,Week, SalesDate,SalesDate={">=$(vPreviousQuarterStart)<=$(vPreviousQuarterEnd)"}>}SalesAmount)

[alexshaji]

Thanks Kush. I am getting the Sum of current and previous qtrs exactly as I need. One more thing that I would request is the Header for this which also should be Dynamic.

So in the KPI i can use 

FY20 Q4                      FY20 Q3

69369                          7789

[Kushal_Chawda]

may be below

// Current Quarter
='FY'& date(quarterstart(max(SalesDate),0,11),'YY')&' '&
'Q'&ceil(month(quarterstart(max(SalesDate),0,11))/3)

// Previous Quarter
='FY'& date(quarterstart(max(SalesDate),-1,11),'YY')&' '&
'Q'&ceil(month(quarterstart(max(SalesDate),-1,11))/3)