Dynamic Aggregation and Calculation Based on Date Slicer Selections

[ebeery]

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!

[amitchandak]

@ebeery , refer if my blog can help

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[ebeery]

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]

@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])

[ebeery]

@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.