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

[SophiaColli]

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!

[v-shex-msft · Accepted solution]

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

[amitchandak]

@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

DATEADD(_st ,QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)

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
https://www.youtube.com/watch?v=8-TlVx7P0A0

[SophiaColli]

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

DATEADD(_st ,QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)

[amitchandak]

@SophiaColli , Try like

Qtr Start Date =

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

return

DATEADD(_st ,QUOTIENT(DATEDIFF(_st , 'Date'[Date],MONTH),3)*3,MONTH)

[SophiaColli]

@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?