Skip to main content
cancel
Showing results for 
Search instead 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

Reply
SophiaColli
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
v-shex-msft
Community Support
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.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
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.
amitchandak
Super User
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

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

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)

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors