Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Our fiscal calendar starts in Jul, and now we are in the Q3. However, I have calculated the Q2 but now show the text somewhere at the bottom of the KPS stating this is Q2 data.
How should I point back to show it is Q2 , and again when I am in Q4, it should change it to Q3 and so on..
i am using the calendar table.
Thanks,
Solved! Go to Solution.
Hi @Anonymous
I think your fiscal year is from July to June next year. Y
our requirement is that when your maxdate in calendar is before the end of the qtr, it will show Qtr -1 and the year in Qtr -1.
Due to I don't know your data model, I build a sample to have a test.
My Sample Table:
Calendar =
VAR _DATE =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 7, 01 ), TODAY() ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] )
)
VAR _FYDate =
ADDCOLUMNS (
_DATE,
"FY Year",
IF ( [Month] < 7, [Year] - 1, [Year] ),
"FY Qtr",
SWITCH (
TRUE (),
[Month] IN { 7, 8, 9 }, 1,
[Month] IN { 10, 11, 12 }, 2,
[Month] IN { 1, 2, 3 }, 3,
4
),
"FY End of Month",
IF ( MOD ( [Month], 3 ) = 0, [Month], [Month] + 3 - MOD ( [Month], 3 ) )
)
RETURN
_FYDate
Add a calculated column in this table.
FY End of Date = DATE([Year],[FY End of Month],IF([FY End of Month] in {3,12},31,30))
Then I build a measure:
Measure =
IF (
MAX ( 'Calendar'[Date] ) < MAX ( 'Calendar'[FY End of Date] ),
IF (
MONTH ( MAX ( 'Calendar'[FY End of Date] ) ) = 3,
"Qtr"
& MAX ( 'Calendar'[FY Qtr] ) - 1 & " " & "Y"
& FORMAT ( MAX ( 'Calendar'[Date] ), "YY" ) - 1,
"Qtr"
& MAX ( 'Calendar'[FY Qtr] ) - 1 & " " & "Y"
& FORMAT ( MAX ( 'Calendar'[Date] ), "YY" )
),
"Qtr" & MAX ( 'Calendar'[FY Qtr] ) & " " & "Y"
& FORMAT ( MAX ( 'Calendar'[Date] ), "YY" )
)
Result is as below.
Max date is 2021/1/11(Today)
If Max date = 2021/4/01(QTR 3 gets over)
You can download the pbix file from this link: Previous quarter
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Make sure you FY end of Qtr , Qtr name or month name are there in calendar table and try measure like
Measure =
var _max = maxx(allselected(Data), Data[Date])
return
calculate(max('Date'[Qtr Name]), filter('Date',Data[Date] =_max))
Hi @amitchandak and @Ashish_Mathur ,
Thanks for your reply. Sorry if I was not clear in my post.
I am using the rank approach. You had provided me these steps sometimes back in my other post.. and it is helping me a lot whenever I have to play with time intelligence.
However, this time I have to show the text. in the card showing the data is for last quarter and not this month quarter. the highlighted one. It should tell me the data is for QTR2 year 2020.. but as soon as the current QTR 3 gets over, it should show QTR3 Y2021.
can this be done?
I am using something like this to show for Previous month. But cannot figure out for Quarter:
and this gives me correct data
Thanks,
@Anonymous , Make sure you FY end of Qtr , Qtr name or month name are there in calendar table and try measure like
Measure =
var _max = maxx(allselected(Data), Data[Date])
return
calculate(max('Date'[Qtr Name]), filter('Date',Data[Date] =_max))
Hi @Anonymous
I think your fiscal year is from July to June next year. Y
our requirement is that when your maxdate in calendar is before the end of the qtr, it will show Qtr -1 and the year in Qtr -1.
Due to I don't know your data model, I build a sample to have a test.
My Sample Table:
Calendar =
VAR _DATE =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 7, 01 ), TODAY() ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] )
)
VAR _FYDate =
ADDCOLUMNS (
_DATE,
"FY Year",
IF ( [Month] < 7, [Year] - 1, [Year] ),
"FY Qtr",
SWITCH (
TRUE (),
[Month] IN { 7, 8, 9 }, 1,
[Month] IN { 10, 11, 12 }, 2,
[Month] IN { 1, 2, 3 }, 3,
4
),
"FY End of Month",
IF ( MOD ( [Month], 3 ) = 0, [Month], [Month] + 3 - MOD ( [Month], 3 ) )
)
RETURN
_FYDate
Add a calculated column in this table.
FY End of Date = DATE([Year],[FY End of Month],IF([FY End of Month] in {3,12},31,30))
Then I build a measure:
Measure =
IF (
MAX ( 'Calendar'[Date] ) < MAX ( 'Calendar'[FY End of Date] ),
IF (
MONTH ( MAX ( 'Calendar'[FY End of Date] ) ) = 3,
"Qtr"
& MAX ( 'Calendar'[FY Qtr] ) - 1 & " " & "Y"
& FORMAT ( MAX ( 'Calendar'[Date] ), "YY" ) - 1,
"Qtr"
& MAX ( 'Calendar'[FY Qtr] ) - 1 & " " & "Y"
& FORMAT ( MAX ( 'Calendar'[Date] ), "YY" )
),
"Qtr" & MAX ( 'Calendar'[FY Qtr] ) & " " & "Y"
& FORMAT ( MAX ( 'Calendar'[Date] ), "YY" )
)
Result is as below.
Max date is 2021/1/11(Today)
If Max date = 2021/4/01(QTR 3 gets over)
You can download the pbix file from this link: Previous quarter
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share some data and show the expected result.
@Anonymous , July is standard Qtr. You should be able to use datesqtd (rollback a qtr) or previous qtrd.
For non standard (which does not start on jan, apr,jul. sep) take rank approch
Non Standard
New columns in date table
Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"6/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
Qtr Month No = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],MONTH)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)
new measures
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))
But these should work in your case
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |