cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Quater to Date for Fiscal/Financial Quater measure in composite model enviroment

Dear PowerBI community,

I am in desperate need for your help!

I am currently working on a report, using composite modeling, which therefore does NOT allow me to calulcate tables or columns - making the following even trickier:

I want to calculate quater to date for fiscal/financial quater. Therefore the function TotalQTD does not work. In my organization the fiscal quater starts in November. How can I build a measure for Quater to Date, considering the quater starts in November, without using a calculated table or column?

Thank you very much!

1 ACCEPTED SOLUTION
Community Support

HI @SophiaColli,

You can write measure with variable to get the corresponding date ranges of the fiscal period. Then you can use returned date ranges as conditions to calculate with your DAX expression.

``````formula =
VAR range =
CALCULATETABLE (
VALUES ( Table[Date] ),
ALLSELECTED ( Table ),
VALUES ( Table[Fiscal Peroid] )
)
RETURN
CALCULATE ( 'expression', FILTER ( ALLSELECTED ( Fact ), [Date] IN range ) )``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
5 REPLIES 5
Community Support

HI @SophiaColli,

You can write measure with variable to get the corresponding date ranges of the fiscal period. Then you can use returned date ranges as conditions to calculate with your DAX expression.

``````formula =
VAR range =
CALCULATETABLE (
VALUES ( Table[Date] ),
ALLSELECTED ( Table ),
VALUES ( Table[Fiscal Peroid] )
)
RETURN
CALCULATE ( 'expression', FILTER ( ALLSELECTED ( Fact ), [Date] IN range ) )``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

@SophiaColli , As long as qtr starts on Jan, Apr, Jul and Oct

We can use datesqtd. After that we need work Qtr No, Start Date and Day of Qtr

example

Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"5/31"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)

better one

Qtr Start Date =

var _st = if(month([date]) >=5 , Date(year([date]),5,1) , Date(year([date])-1,5,1) )

return

Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

measures
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

This QTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Day] <=max([Qtr Day])))
Last QTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Day] <=max([Qtr Day])))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

Frequent Visitor

Hi @amitchandak ,

thank you for coming back to me!

Regarding your solution, I currently do not have "Start of Year" in my date table - how can I use your solution anyways?

Also I get the error message for the DATEADD fuction "The first argument to 'DATEADD' must specify a column"

Thank you!

Qtr Start Date =

var _st = if(month([date]) >=5 , Date(year([date]),5,1) , Date(year([date])-1,5,1) )

return

Super User

@SophiaColli , Try like

Qtr Start Date =

var _st = if(month([date]) >=5 , Date(year([date]),5,1) , Date(year([date])-1,5,1) )

return

Frequent Visitor

@amitchandakas metioned above with DATEADD fuction "The first argument to 'DATEADD' must specify a column" - here we us a variable with var _st

Any suggestion how to solve this?