Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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!
Solved! Go to Solution.
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:
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])
)
)
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.
@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/
Hi again @amitchandak
I did manage to get ADD QT, and the orther calculated columns. However, the measure This QTD gives an error.
Try
This QTD = CALCULATE([Projects Submitted],FILTER(ALL('Calendar'),'Calendar'[Qtr Rank]=MAX('Calendar'[Qtr Rank])))
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/month | Projects submitted | QTD new | YTD submitted |
1 | 7 | 58 | 7 |
Aug | 3 | 58 | 3 |
Sept | 3 | 58 | 6 |
Oct | 1 | 58 | 7 |
2 | 11 | 58 | 18 |
Nov | 6 | 58 | 13 |
Dec | 1 | 58 | 14 |
Jan | 4 | 58 | 18 |
Intead of
Quarter/month | Projects submitted | QTD new | YTD submitted |
1 | 7 | 7 | 7 |
Aug | 3 | 7 | 3 |
Sept | 3 | 7 | 6 |
Oct | 1 | 7 | 7 |
2 | 11 | 11 | 18 |
Nov | 6 | 11 | 13 |
Dec | 1 | 11 | 14 |
Jan | 4 | 11 | 18 |
Thanks.
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:
Thanks. I'm trying this.
What's the formula for
Dates[Add Qtr]
Thanks.
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...
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".
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/month | Projects submitted | QTD new | YTD submitted |
1 | 7 | 7 | 7 |
Aug | 3 | 7 | 3 |
Sept | 3 | 7 | 6 |
Oct | 1 | 7 | 7 |
2 | 11 | 11 | 18 |
Nov | 6 | 11 | 13 |
Dec | 1 | 11 | 14 |
Jan | 4 | 11 | 18 |
thanks for you help.
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |