Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 NumberSales DateSales $Sales Qty
Part A01-01-2020$1.231
Part B01-02-2020$2.342
Part C01-03-2020$3.453
Part A01-04-2020$4.564

 

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

 

PartSales in Period ASales in Period BSales in Both Periods
PartA$10.00$0.00FALSE
PartB$0.00$30.00FALSE
PartC$20.00$40.00TRUE

 

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

    • Anonymous's avatar
      Anonymous
      Not 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.

       

      PartSales $ Period ASales Period BSales Qty ASales Qty BPrice APrice B
      A$10.00$20.001015= $A / Qty A= $B / Qty B
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper 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])