Sale in quarter

[Ashik008]

Hi all,

Please help to find a sql query to calculate the total sales in quarter 

table as follow 

Product	Month number	year	sale
A	2	2022	1000
B	2	2022	2000
c	1	2022	1000
B	4	2022	3000
A	1	2022	1000
c	6	2022	2000

result im looking for 

Product	Quarter	Sale
A	Quarter1	5000
B	Quarter1	2000
C	Quarter1	1000
B	Quarter2	3000
C	Quarter2	2000

please help

[Anonymous · Accepted solution]

Hi @Ashik008 ,

Please try to create a new column with below dax formula:

Quarter =
VAR cur_year = 'Table'[year]
VAR cur_month = 'Table'[Month number]
VAR _date =
    DATE ( cur_year, cur_month, 1 )
RETURN
    "Quarter" & QUARTER ( _date )

Add a table visual with fields:

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ashik008 , I assume you need power bi solution

Create a date with year and month

Date = Date([Year], [Month],1)

Create a date Table like

Calendar = Addcolumns(calendar(date(2012,01,01), date(2024,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(([DAte])) <7 , year(([DAte]))-1 ,year(([DAte])))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

and join with date table

and use qtr column from date and sum(sales) from your table

You can create the different formats of quarters as per need in the date table

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1