Combining Dynamic Query Parameters and Append Queries

Hi Sir Night,

You probably don't need two data tables for this comparison. You could acheive this with two DAX measures and two disconnected calendar tables, in fact you could have one calendar and just use the min and max values to determine the period if you wanted but I'll show you the 2 calendar method for the example.

I have 2 Calendars: Period1 and Period2

I have one Data table with Sales data called "DataTable" - there is a common [datekey] in the tables but no links, no relationships.

First create a measure which calculates the SUM of the Sales for Period1

P1 Sales  = CALCULATE(SUM(Data_Table[Sales]),FILTER(Data_Table, Data_Table[datekey]<=LASTDATE(Period1[datekey]) && Data_Table[datekey]>=FIRSTDATE(Period1[datekey]))))

Now the same pattern for Period 2 sales:

P2 Sales = CALCULATE(SUM(Data_Table[Sales]),FILTER(Data_Table, Data_Table[datekey]<=LASTDATE(Period2[datekey]) && Data_Table[datekey]>=FIRSTDATE(Period2[datekey]))))

so if you drag the two calendars onto the report as slicers you should be able to select any period (day, month, week etc) to compare sales numbers

you could also create a measure like

Variance = DIVIDE([P1 Sales]-[P2 Sales],[P1 Sales],0) to give you % variance or whatever you need.

note I didn't check the syntax on the measures but they should be close:

some reference blogs:

http://excel-and-analytics.blogspot.com.au/2016/02/count-active-contracts-betweenstartdate.html

http://www.daxpatterns.com/budget-patterns/

Cheers

Greg