Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Fiscal Quarter

Hi,

 

I hope you're fine. I trying to calculate TOTALQTD for my financial calendar. I've read a lot but can't find a way to solve this...

 

My fiscal calendar is: 

Q1: Aug-Oct

Q2: Nov-Jan

Q3: Feb-Apr

Q4:May-July

 

My calendar table is here. 

calendar.png

 
 

I want to calculate the measure [projects submitted] for current QTD. 

 

Also, I have a query. I guess the TOTALQTD value can be filtered by the Academic Year and Quarter from a slicer/filter? So the user can choose the date, but if not selected it will always choose the latest QTD? For natural quaters, for my TOTALQTD to show the current QTD, I had to use "Today" (Using Date column) to show current QTD, otherwise it went blank. 

 

Thanks

 

Thanks a lot!

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi all,

Finally I come up with the solution!

Here the solution that worked for me. I had to create calculated columns in my calendar table to create the fiscal quarters:

Start of QTR = IF('Calendar'[Month Number]=8||'Calendar'[Month Number]=9||'Calendar'[Month Number]=10||'Calendar'[Month Number]=11||'Calendar'[Month Number]=12,DATE(YEAR('Calendar'[Date]),'Calendar'[Month of the quarter],1),IF('Calendar'[Month Number]=1,DATE(YEAR('Calendar'[Date])-1,'Calendar'[Month of the quarter],1),IF('Calendar'[Month Number]=2||'Calendar'[Month Number]=3||'Calendar'[Month Number]=4||'Calendar'[Month Number]=5||'Calendar'[Month Number]=6||'Calendar'[Month Number]=7,DATE(YEAR('Calendar'[Date]),'Calendar'[Month of the quarter],1))))
 
End of Qtr = EOMONTH('Calendar'[Start of QTR],2)
 
Then I created the DAX measures:
QTD Projects Submitted = CALCULATE([Projects Submitted],DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Start of QTR]),MAX('Calendar'[End of Qtr])))
 
Hope this helps. 

 

View solution in original post

9 REPLIES 9
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

 

Like this?

Measure = 
CALCULATE(
    SUM(Sheet2[Value]),
    FILTER(
        ALL(Sheet2),
        Sheet2[Financial _Quarter] = SELECTEDVALUE(Sheet2[Financial _Quarter]) && Sheet2[Financial_Date] <= SELECTEDVALUE(Sheet2[Financial_Date])
    )
)

mn2.PNG

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , Add the following to your date table

Start of Year = STARTOFYEAR(Dates[Date],"7/31")
Qtr No = "Q"& QUOTIENT(DATEDIFF(Dates[Start of Year], Dates[Date],MONTH),3)+1
Start of Qtr = date(year(Dates[Start of Year]), month(Dates[Start of Year])+Dates[Add Qtr],1)
Qtr Rank = RANKX(ALL(Dates),Dates[Strat of Qtr],,ASC,Dense)
Qtr = QUOTIENT(DATEDIFF(Dates[Start of Year], Dates[Date],MONTH),3)+1

 

Create measures like this

This Qtr = CALCULATE([Total Value], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank])))
last Qtr = CALCULATE([Total Value], FILTER(ALL(Dates), Dates[Qtr Rank] =max(Dates[Qtr Rank])-1))	

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

Anonymous
Not applicable

Hi again @amitchandak 

 

I did manage to get ADD QT, and the orther calculated columns. However, the measure This QTD gives an error.

 

This QTD = CALCULATE([Projects Submitted],FILTER(ALL('Calendar'[Date]),'Calendar'[Qtr Rank]=MAX('Calendar'[Qtr Rank])))
 
Error: A single value for column Qtr Rank in table "Calendar" cannot be determined. This can happen when a measure formual refers to a column that contains many values withouht specifying an agregation such as min, max, count or sum to get a single results.
 
If I take out FILTER, as
CALCULATE([Projects Submitted],ALL('Calendar'[Date]),'Calendar'[Qtr Rank]=MAX('Calendar'[Qtr Rank]))
Then I have the error that a function MAX has been used in a True/False expression that is used as a table expression.
 
Also, the column Rank Qtr gives only the value 1... I don't know if this is normal?
 
any help please? 

Try

This QTD = CALCULATE([Projects Submitted],FILTER(ALL('Calendar'),'Calendar'[Qtr Rank]=MAX('Calendar'[Qtr Rank])))

Anonymous
Not applicable

Thanks @amitchandak 

 

The formula worked this time, but it's giving me wrong values, see the table. Maybe because the Qtr Rank in the calendar table gives only 1 value (regardles the date)?

 

Quarter/monthProjects submittedQTD newYTD submitted
17587
Aug3583
Sept3586
Oct1587
2115818
Nov65813
Dec15814
Jan45818

 

Intead of 

Quarter/monthProjects submittedQTD newYTD submitted
1777
Aug373
Sept376
Oct177
2111118
Nov61113
Dec11114
Jan41118

 

Thanks.

Anonymous
Not applicable

Hi all,

Finally I come up with the solution!

Here the solution that worked for me. I had to create calculated columns in my calendar table to create the fiscal quarters:

Start of QTR = IF('Calendar'[Month Number]=8||'Calendar'[Month Number]=9||'Calendar'[Month Number]=10||'Calendar'[Month Number]=11||'Calendar'[Month Number]=12,DATE(YEAR('Calendar'[Date]),'Calendar'[Month of the quarter],1),IF('Calendar'[Month Number]=1,DATE(YEAR('Calendar'[Date])-1,'Calendar'[Month of the quarter],1),IF('Calendar'[Month Number]=2||'Calendar'[Month Number]=3||'Calendar'[Month Number]=4||'Calendar'[Month Number]=5||'Calendar'[Month Number]=6||'Calendar'[Month Number]=7,DATE(YEAR('Calendar'[Date]),'Calendar'[Month of the quarter],1))))
 
End of Qtr = EOMONTH('Calendar'[Start of QTR],2)
 
Then I created the DAX measures:
QTD Projects Submitted = CALCULATE([Projects Submitted],DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Start of QTR]),MAX('Calendar'[End of Qtr])))
 
Hope this helps. 

 

Anonymous
Not applicable

Thanks. I'm trying this. 

What's the formula for 

Dates[Add Qtr]

 

Thanks. 

Greg_Deckler
Super User
Super User

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Greg,

 

Thanks for your prompt response. I did read the post, and... I couldn't get it. the measure (qtd new) behaves as TOTALYTD (see the table) even if I changed the formula to "Quarters". 

 

Annotation 2020-05-18 160330.png

This is what I sued for QTD new:

QTD new =

    var MaxYear = MAX('Calendar'[Inital year])

    var MaxQuarter = MAX('Calendar'[Academic Quarter])

    var TmpTable = CALCULATETABLE('Calendar',ALL('Calendar'[Inital year]),ALL('Calendar'[Academic Quarter]))

    return SUMX(FILTER(TmpTable,'Calendar'[Inital year]=MaxYear && 'Calendar'[Academic Quarter] <= MaxQuarter),[Projects Submitted])

 

This table should look like this

 

Quarter/monthProjects submittedQTD newYTD submitted
1777
Aug373
Sept376
Oct177
2111118
Nov61113
Dec11114
Jan41118

 

thanks for you help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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