The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi all,
I'm struggling with a dynamic aggregation which I'm pretty confident should be possible with DAX, but as of yet has proved too difficult for my current abilities....
What I'm basically trying to achieve is this:
From a table of sales transactions structured as below:
Part Number | Sales Date | Sales $ | Sales Qty |
Part A | 01-01-2020 | $1.23 | 1 |
Part B | 01-02-2020 | $2.34 | 2 |
Part C | 01-03-2020 | $3.45 | 3 |
Part A | 01-04-2020 | $4.56 | 4 |
I would like to perform several subsequent calculations based on the total sales ($ & qty) for each part number in "Period A" and "Period B", where Period A and Period B are dynamic and defined based on two "Between" Date slicers on the report page.
Period selection slicers on report page
What I've tried so far is to generate a calculated "aggregated" table using SUMMARIZECOLUMNS, and then create additional calculated columns based on that table. However, with the aggregation being a calculated table (as opposed to being contained within a measure) it won't respond to the date slicer selections, so for now I'm hard-coding the dates into the aggregated table formula as below.
Aggregated Table =
VAR PeriodA = DATESBETWEEN(vw_DIM_Dates[Date_Key], DATE(2020,02,01),DATE(2020,02,29))
VAR PeriodB = DATESBETWEEN(vw_DIM_Dates[Date_Key], DATE(2020,03,01),DATE(2020,03,31))
RETURN
SUMMARIZECOLUMNS(...
The next approach I tried was to calculate the table "virtually" within a measure using various combinations of SUMMARIZECOLUMNS(), SUMMARIZE()/ADDCOLUMNS(), GROUPBY(), etc. etc. etc., all which failed to sucessfully produce the desired effect of calculating a table such as below with the total sales in Period A and total sales in Period B, "virtually", so that I can make comparison calculations across the two periods. As an example, the first and most basic measure that I would like to calculate is the "Total Sales in Period A for Parts which also have sales in Period B".
Part | Sales in Period A | Sales in Period B | Sales in Both Periods |
PartA | $10.00 | $0.00 | FALSE |
PartB | $0.00 | $30.00 | FALSE |
PartC | $20.00 | $40.00 | TRUE |
I feel that the solution likely lies in some combination of SUMMARIZECOLUMNS() and CALCULATE(), but so far I haven't been able to work it out. Initial attempts yielded the correct results when calculating either Sales in Period A or Sales in Period B, but when both were incorporated into the same measure, the Period A totals showed for both, leading me to believe there was some issue with the way the CALCULATE filter context was working...
Any insight or recommendations on alternate approaches would be much appreciated.
Thanks!
@ebeery , refer if my blog can help
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @amitchandak , thanks for the help.
I understand the method you reference of harvesting the slicer selections to calculate the sales for the 2 periods independently.
However where I'm still struggling is how to then to combine the results (in your example "Current Sales" and "Prior Sales") into a single virtual table to perform comparison calculations on the totals, aggregated to a part number level.
For example, the simple "Price" calculations in the last two columns of the table below.
Part | Sales $ Period A | Sales Period B | Sales Qty A | Sales Qty B | Price A | Price B |
A | $10.00 | $20.00 | 10 | 15 | = $A / Qty A | = $B / Qty B |
@ebeery , other then time it will combine across common dimension and you can take diff as usual betwen two measures
diff = [period 1] - [period 2]
diff % = divide([period 1] - [period 2], [period 2])
@amitchandak, the granularity of the calculations needs to be at the part number level, which is why I'm trying to aggregate at that level. I'm not seeing how that can be achieved with simply using the high-level measures you're describing. Maybe I'm missing something.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
95 | |
84 | |
66 | |
61 |
User | Count |
---|---|
246 | |
124 | |
115 | |
79 | |
78 |