fiscal QTD and fiscal QTD same period last year

[rmudgal89]

Hello All ,

i am facing problem while calculating Sales amount for Fiscal QTD as my fiscal year starts in Nov , didn't find a single solution to this problem

Any help will be appreciated

Thanks

For example 

Month	Calendar Year	Fiscal Year	Fiscal Quarter
Nov	2019	2020	Q1
Dec	2019	2020	Q1
Jan	2020	2020	Q1
Feb	2020	2020	Q2
Mar	2020	2020	Q2

[lbendlin]

It's actually really simple. 

1. Use an external Dates/Calendar table with your fiscal period details  -can be an exel file or a SQL server table. (Don't listen to anyone proposing to do this in Power Query or DAX.  It will not work in the long run. Feel free to ignore my advice and repeat my mistakes.  BTW our fiscal year also starts in November)

2. Most date intelligence functions in DAX will work as is. The only issue are the quarterly computations. Those you have to do yourself.

3. Pay special attention to the YoYTD calculations where you compare the current quarter to the same quarter last year but you want to compare the same time intervals.  In that case you need to add a calculated column to your Dates table.

IsPastPY = 
VAR LastSalesDatePY = EDATE(MAX('FactTable'[Day]),-12)
RETURN [date]<=LastSalesDatePY

And then for YoYTD you add that as a filter. Here is the measure for Previous Year sales up to the same day as your last sales this year:

PY: = CALCULATE (
            [Value],
            SAMEPERIODLASTYEAR(Dates[date]),
            Dates[IsPastPY]=TRUE
        )

[rmudgal89]

 Hi lbendlin 

Thanks for reply ,but this is not working for me 

For my requirement i need to show QTD delta and % column for customers .

[harshnathani]

Hi @rmudgal89 ,

Try this.

You will need a Date Table

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD('Date'[Date]))

last year QTD  Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

[rmudgal89]

Hi Harsh ,

This do not solve my purpose , i am looking for calculating Fiscal year quarter comparison and my oraganisation Quarter starts from Nov i.e (Nov , dec , Jan ) is Q1 for me