Forum Discussion

csymons's avatar
csymons
New Member
6 years ago
Solved

QTD

Hi,

I'm trying to figure out how to calculate quarterly sales when I don't have a calendar year-end. I want the user to be able to select month and year they want then press a button for Qtr to get quarterly sales. I know how to get the button selector to choose quarterly sales but not how to calculate quarterly based on year/month selected. How would I do this?

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Is qtr is standard calendar . means Apr-Jun, Jul-Sep etc. Better you can use Date calendar and time intelligence

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

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

     

    If not, Have calendar table when you have Qtr and create a rank on qtr and use the rank . Refer this week example of Using rank

     

    https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

     

    Also, vote for Idea of non-standard Qtr

    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39772834-custom-period

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Is qtr is standard calendar . means Apr-Jun, Jul-Sep etc. Better you can use Date calendar and time intelligence

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

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

     

    If not, Have calendar table when you have Qtr and create a rank on qtr and use the rank . Refer this week example of Using rank

     

    https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

     

    Also, vote for Idea of non-standard Qtr

    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39772834-custom-period