Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Dear PowerBI community,
First of all I would like to say hello, as it's my first post here. 😉
To my main table, I have attached query which extract sales data aggregated with product, end date of the month, country, section, AVG(price) of the sales, AVG(cost), SUM(quantity of sold articles) and a few another columns, which are not that important here.
I want to create a new table based on previous, but aggregated not on article level.
The code looks similarly to below,
As you may notice, I want to have the data only for last 365 days. The second filter I would apply is, take into consideration only articles, which were sold in such country, supplier_type, section within all last 12 months.
Maybe someone can help to achieve it?
Solved! Go to Solution.
@PawelTr
Please try
Aggregated =
VAR T1 =
FILTER ( Fact_Table, Fact_Table[INVOICE_MONTH_END] >= TODAY () - 365 )
VAR T2 =
SUMMARIZE (
T1,
Fact_Table[Article No.],
"@CounMonths", COUNTROWS ( VALUES ( Fact_Table[INVOICE_MONTH_END] ) )
)
VAR T3 =
FILTER ( T2, [@CounMonths] = 12 )
VAR T4 =
FILTER ( T1, Fact_Table[Article No.] IN T3 )
VAR Result =
SUMMARIZE (
T4,
Fact_Table[INVOICE_MONTH_END],
Fact_Table[COUNTRY],
Fact_Table[SECTION],
Fact_Table[Supplier_type],
"Sum_cost_price", SUM ( Fact_Table[Quantity_sold] ) * SUM ( Fact_Table[Cost_Price] ),
"Sum_selling_price", SUM ( Fact_Table[Quantity_sold] ) * SUM ( Fact_Table[Selling_price] ),
"Sum_quantity", SUM ( Fact_Table[Quantity_sold] )
)
RETURN
Result
@PawelTr
Please try
Aggregated =
SUMMARIZE (
FILTER ( Fact_Table, Fact_Table[INVOICE_MONTH_END] >= TODAY () - 365 ),
Fact_Table[INVOICE_MONTH_END],
Fact_Table[COUNTRY],
Fact_Table[SECTION],
Fact_Table[Supplier_type],
"Sum_cost_price", SUM ( Fact_Table[Quantity_sold] ) * SUM ( Fact_Table[Cost_Price] ),
"Sum_selling_price", SUM ( Fact_Table[Quantity_sold] ) * SUM ( Fact_Table[Selling_price] ),
"Sum_quantity", SUM ( Fact_Table[Quantity_sold] )
)
Thank you tamerj1 for reply!
Maybe I wasn't specific enough, but what I would like to add to the code is to take into consideration only articles, which were sold in such country, supplier_type, section within all last 12 months.
If no rows exist for article for specific country with e.g. April, it won't be included in the summarize aggregation.
As I understand it correctly, if sale of article doesn't exist in April, but does in May, quantity will be included in row of May.
What I'm trying to achieve is,
sales needs to exist within all last 12 months. If it did not existed during the one, or more of those months, it shouldn't be included in any.
@PawelTr
Please try
Aggregated =
VAR T1 =
FILTER ( Fact_Table, Fact_Table[INVOICE_MONTH_END] >= TODAY () - 365 )
VAR T2 =
SUMMARIZE (
T1,
Fact_Table[Article No.],
"@CounMonths", COUNTROWS ( VALUES ( Fact_Table[INVOICE_MONTH_END] ) )
)
VAR T3 =
FILTER ( T2, [@CounMonths] = 12 )
VAR T4 =
FILTER ( T1, Fact_Table[Article No.] IN T3 )
VAR Result =
SUMMARIZE (
T4,
Fact_Table[INVOICE_MONTH_END],
Fact_Table[COUNTRY],
Fact_Table[SECTION],
Fact_Table[Supplier_type],
"Sum_cost_price", SUM ( Fact_Table[Quantity_sold] ) * SUM ( Fact_Table[Cost_Price] ),
"Sum_selling_price", SUM ( Fact_Table[Quantity_sold] ) * SUM ( Fact_Table[Selling_price] ),
"Sum_quantity", SUM ( Fact_Table[Quantity_sold] )
)
RETURN
Result
Wow, that was amazing, that's the code which I was struggling to create.
Big thank you!!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |