Forum Discussion

BItoken's avatar
BItoken
Icon for Helper III rankHelper III
6 years ago
Solved

Display last 4 quarters sum dynamically

 
 

Hi,

 

I need to display a column "To Do" only to display last 4 quaters of 'To do sales' in the table.

I have a Fiscal Year Column available.

Please help!!

  

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi BItoken ,

     

    We can use the following steps to meet your requirement.

     

    1. Create year and quarter columns.

     

    Year = YEAR('Table'[Date])
Quarter = QUARTER('Table'[Date])

     

    2. We can create a measure to calculate the last year quarter to do sales.

     

    To Do = 
var x = MAX('Table'[Year])
var y = MAX('Table'[Quarter])
return
CALCULATE(SUM('Table'[To Do Sales]),FILTER(ALLSELECTED('Table'),'Table'[Year]=x-1 && 'Table'[Quarter]=y))

     

     

    If it doesn’t meet you requirement, could you please show the exact expected result based on the table that we have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    If you have the date, you can use 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 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))))

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

     

    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/

     

    In case you do not have date, you need use Qtr rank to do it. please check this week file how to do that

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

     

    I am hosting a webinar on 25th April on Power BI, Check Details - https://www.linkedin.com/posts/amitchandak78_webinar-tech-techforgood-activity-6658266754378231808-ye5L

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi BItoken ,

     

    We can use the following steps to meet your requirement.

     

    1. Create year and quarter columns.

     

    Year = YEAR('Table'[Date])
Quarter = QUARTER('Table'[Date])

     

    2. We can create a measure to calculate the last year quarter to do sales.

     

    To Do = 
var x = MAX('Table'[Year])
var y = MAX('Table'[Quarter])
return
CALCULATE(SUM('Table'[To Do Sales]),FILTER(ALLSELECTED('Table'),'Table'[Year]=x-1 && 'Table'[Quarter]=y))

     

     

    If it doesn’t meet you requirement, could you please show the exact expected result based on the table that we have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.