Forum Discussion
Dynamic Aggregation and Calculation Based on Date Slicer Selections
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!
4 Replies
- amitchandak
Super User
Anonymous , refer if my blog can help
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
- AnonymousNot applicable
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 - amitchandak
Super User
Anonymous , 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])