QTD Fuction for Financial Year calendar

[sujitjena]

Hi All,

I am new to the QTD function that is introduced in Power Bi for Time intelligent functions. For Some reason it doesnt follow the financial year that i have defined (CalendarAuto). It goes back to Calendar Year format. Is there a specific argument that i am missing here. Here are the functions i tried 

CALCULATE((SUM(sales[sales],DATESQTD(Dates[Date]),Sales[Dimension]="Actual")

TOTALQTD(SUM(Sales[Sales]),Dates[Date])

Any suggestions?

[amitchandak]

@sujitjena , Try like

CALCULATE(SUM(sales[sales]),DATESQTD(Dates[Date]),Sales[Dimension]="Actual")

Also with datesqtd you need to have date selected

Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

[sujitjena]

@amitchandak - Thanks for the quick reply but this only works with normal calendar quarters. For me the Financial Quarter starts in Sep.

[parry2k]

@sujitjena TOTALQTD and DATESQTD function are not fiscal/financial year calendar aware. you have to manage your own calculation for QTD to meet your financial calendar, although YTD functions are fiscal year aware as you can pass your own fiscal year-end value to it.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[sujitjena]

@parry2k : Thanks for a quick response. Is there a way to make these functions calendar aware? because i thought once i have defined my quarters in the CalendarAuto table, the QTD function will follow the same.

[v-xuding-msft]

Hi @sujitjena ,

You could try like this:

Create a fiscal calendar table:

https://goodly.co.in/financial-year-calculation-excel-and-power-bi/

Create a QTD measure:

Measure = CALCULATE (
[Total Amount],
FILTER (
ALL ( ‘Calendar’ ),
‘Calendar'[FiscalYear] = MIN ( ‘Calendar'[FiscalYear] )
&& ‘Calendar'[FiscalQuarter] = MIN ( ‘Calendar'[FiscalQuarter] )
&& ‘Calendar'[Date] <= MAX ( ‘Calendar'[Date] )
)

[pedrovillegas]

Thanks, I was looking for a solution like this for so long.

[sujitjena]

@v-xuding-msft : Thank you for the solution and i am using this already. But i was wondering if Microsoft indroduced QTD function, it should be able to accomodate financial calanders for Quarters like the YTD function, else its of less use.

[Ashish_Mathur]

Hi,

In the Calendar Table, you will have to create a Quarter column.  Share the link from where i can download your PBI file.