Forum Discussion

RonaldW's avatar
RonaldW
New Member
6 years ago

Computing Consolidated Growth Rates from Disaggregated Data

Month on Month/Year on Year Growth

Aiming to:

  • Calculate year-on-year and month on month growth at a store level as well as at a consolidated level.
  • Currently using one table that has  revenues, transactions and average spend broken-down by store and delivery type.

The Issue

  • Using data from the same table, I want to be able to calculate the month on month growth and year on year growth, both on a consolidated and store-by-store level and display it on Power BI charts.
  • To calculate this on a consolidated level, Power currently calculates the average growth rates for all the stores, which is not correct.

I want Power BI  to sum the data for the stores (revenues, transactions) for the current month and compare it with that of the last month. Not take the average growth for all the stores. 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Did not got completely. But You can use Time Intelligence and Date calendar to get YTD,MTD and YOY etc

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last year MTD Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))


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


YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    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 :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

     

    To force dates :https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

     

    Appreciate your Kudos.

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      RonaldW Does the above replies helps. if you need more help make me @

      Appreciate your Kudos.

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    Hi RonaldW 

     

    You might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.