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

Previous quarter

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,

 

2 ACCEPTED SOLUTIONS

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

1.png

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)

2.png

If Max date = 2021/4/01(QTR 3 gets over)

3.png

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. 

View solution in original post

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi @amitchandak and @Ashish_Mathur  ,

Thanks for your reply. Sorry if I was not clear in my post.

 

@amitchandak 

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.

tejaswidmello_0-1610227103441.png

can this be done?

I am using something like this to show for Previous month. But cannot figure out for Quarter:

 

FY-Current Month End =
VAR MaxMonth = FORMAT( CALCULATE( MAX( Calendar[Date]), FILTER(ALL('Calendar'),'Calendar'[FY- Month_Rank]<max(Calendar[FY- Month_Rank]))), "MMM" )
VAR MaxYear = FORMAT( CALCULATE( MAX( Calendar[Date] ), FILTER(ALL('Calendar'),'
Calendar'[FY- end of Month]<max(Calendar[FY- end of Month]))), "YY" )
RETURN
MaxMonth & " " &"FY"&MaxYear

 

and this gives me correct data 

 
 

tejaswidmello_3-1610227756972.png

 

 

 

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

1.png

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)

2.png

If Max date = 2021/4/01(QTR 3 gets over)

3.png

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. 

Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

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

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.