Forum Discussion

johnmelbourne's avatar
6 years ago
Solved

Compare same quarter last year

I have data at different granularity level and want to compare quarter on quarter.

2019 is in quarter format and has a date of 30/6, 30/8 etc.

2020 is monthly.

 

I have attached the pbix here: https://drive.google.com/file/d/1CuUzMfmgL7YQU6B5puTx_q0aVy287puk/view?usp=sharing

 

The erroring measure is the issue. I think I have the current (max) year measure correct.

 

Quarter value (CY) =
CALCULATE (
// Calculate
[QTR (sum value)],
// the sum of all values in the Val column of the Data table
FILTER (
'Calendar',
'Calendar'[Year] = [Max Year]
))
 
Measure 1 error code:
 
Measure 1 =
CALCULATE (
// Calculate
[QTR (sum value)],
// the sum of all values in the Val column of the Data table
FILTER (
'Calendar', 'Calendar'[Year]),
'Calendar'[Year] = [Max Year - 1]
)
 
Any assistance greatly appreciated
 

 

  • johnmelbourne , In case you have a date. You can use time intelligence with date table

     

    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 complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
    Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
    Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
    Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
    Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))

     

     

    If you do not have a date, the create a qtr table and use Rank on the qtr  start date or YYYYQ , for a solution like below

     

    Columns

    Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
    Qtr Month No = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],MONTH)+1Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

     

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

     

    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 :radacad sqlbi My Video Series Appreciate your Kudos.

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.

     

2 Replies

  • johnmelbourne , In case you have a date. You can use time intelligence with date table

     

    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 complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
    Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
    Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
    Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
    Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))

     

     

    If you do not have a date, the create a qtr table and use Rank on the qtr  start date or YYYYQ , for a solution like below

     

    Columns

    Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
    Qtr Month No = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],MONTH)+1Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

     

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

     

    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 :radacad sqlbi My Video Series Appreciate your Kudos.

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.

     

    • johnmelbourne's avatar
      johnmelbourne
      Icon for Helper V rankHelper V

      Thnak you so much. They are patterns I will be re-using over and over!

      John