Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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.
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.
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
206 | |
90 | |
61 | |
59 | |
57 |