Anonymous

Not applicable

5 years ago

Solved

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,

Need Help

Anonymous

5 years ago

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.

amitchandak
5 years ago
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))

5 Replies

amitchandak

Super User

5 years ago

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

Ashish_Mathur
Super User
5 years ago
Hi,

Share some data and show the expected result.
Anonymous
Not applicable
5 years ago
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.

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

Thanks,
- Anonymous
  Not applicable
  5 years ago
  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.
- amitchandak
  Super User
  5 years ago
  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))

Forum Discussion

Previous quarter

5 Replies

Related content

How to get previous quarter's previous quarter data?

Previous Quarter Comparison

Previous Quarter

calculate % difference from previous quarter

Previous Year/quarter sales