Forum Discussion
Helper ICalculate Quarter Sales
Hi All,
I'm tryin?g to calculate the sum per quarter of a sales revenue but I can't figure out the best way to compute it. May I know of the best way how?
I have 2 columns table forexample below and want to get the total per quarter.
Revenue Quarter
100 Q1
200 Q1
300 Q1
330 Q2
400 Q2
600 Q2
600 Q3
300 Q3
200 Q4
100 Q4
Thanks!
HI norken20,
Any other categories or fields are you used in calculation? If this is a case, please explain more detail about your data structure.
How to Get Your Question Answered Quickly
If not, you can simply use 'quarter' field right part as filter conditions to calculate.Measure = CALCULATE ( SUM ( Table[Revenue] ), FILTER ( ALLSELECTED ( Table ), RIGHT ( Table[Quarter], 1 ) <= RIGHT ( MAX ( Table[Quarter] ), 1 ) ) )Regards,
Xiaoxin Sheng
4 Replies
- amitchandak
Super User
Make sure you have a date table and date table can have a column Qtr name to sum data like this. You can also use time intelligence function for that
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date]))) Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,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)))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/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
norken20Thanks!
HI norken20,
Any other categories or fields are you used in calculation? If this is a case, please explain more detail about your data structure.
How to Get Your Question Answered Quickly
If not, you can simply use 'quarter' field right part as filter conditions to calculate.Measure = CALCULATE ( SUM ( Table[Revenue] ), FILTER ( ALLSELECTED ( Table ), RIGHT ( Table[Quarter], 1 ) <= RIGHT ( MAX ( Table[Quarter] ), 1 ) ) )Regards,
Xiaoxin Sheng
- Ashish_Mathur
Hi,
Drag Quarter to your visual and write this measure
=SUM(Data[Revenue])
Hope this helps.
