Combining Dynamic Query Parameters and Append Queries

[Sir_night]

Ive been asked to creat a report that allows the user to compare performace against any months i.e Jan 2014 Vs Feb 2017

i've created a two identical tables with the data in and two Dynamic Querys one per table,

What is the best to combine these tables? so i can display the data for the months selected

[dearwatson · Accepted solution]

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

[Sir_night]

hi Greg

is there any way to use this to create a detail table, I tried using the below query 

Period2-Data = CALCULATETABLE('Snap Shot End of Month',FILTER('Snap Shot End of Month',[SnapshotDate].[Date]<=LASTDATE('Calender Two'[Date 2].[Date])&&'Snap Shot End of Month'[SnapshotDate]>=FIRSTDATE('Calender Two'[Date 2].[Date])))

[dearwatson]

Yes but I'm not 100% across how this works, post it as a new question on the forum and someone will give you an answer

[GilbertQ]

Hi @Sir_night

What I would suggest is to Append the tables together, then as long as you got it in the same table you can start to do comparison's.

Ideally it would be best to create a date table, which will allow you to compare the data side by side in your visuals?

[Sir_night]

The problem with the append queries into new table is that it removes the Dynamic query Parameters selection and brings back every thing.

[GilbertQ]

I am not exactly sure what you are doing, but it should keep your existing dynamic query parameters in your original tables?

[Sir_night]

working Now