Creation of report with 2 filters

[Krishnakali]

Hi all,

Please help me.

I have to create a report where I should have region, sales as on date 1, sales as on date 2 and difference bewteen sales. Date 1 should be selected from filter 1 and date 2 from filter 2. Also, if no region has sales on date 1, then it should diaplay as 0. The region should not get filtered out if it has sales on date 2. 

Currently I have 2 slicers for the 2 dates, and 2 tables with region and sales in each table. Each slicer is filtering only 1 table each. But I want everything to be in a single table, and basically keeping region unaffected by any filters and just displaying sales as per the 2 dates. I hope I have been able to state my problem. Kindly look into it.

[v-yuezhe-msft]

@Krishnakali,

It is difficult for us to provide you approach based on the above description. Could you please share sample data of your tables and post expected result based on the sample data?

Regards,
Lydia

[Krishnakali]

HI, My input data is as follows:

region	balance	date
north	100	01-01-2000
south	101	01-01-2000
east	102	03-01-2000
west	103	04-01-2000
north	104	05-01-2000
east	105	06-01-2000
east	106	07-01-2000

My report should contain 2 date filters. User should be able to choose any date he/she wants.

My output should look like:

				date 1 filter	01-01-2000
				date 2 filter	05-01-2000
region	balance on date 1	balance on date 2	difference
north	100	104	-4
south	101	0	101
east	0	0	0
west	0	0	0

I have created 2 date filters using 2 slicers. But I am not able to keep region in a single table. I could create 2 tables with balance region,balance on date1 & region,balance on date 2. I created a measure which is difference between balance 1 and 2, and kept it in a separate table with region, but the region gets filtered out based on date selected in slicer. For eg, if i select 01-01-2000, only north and south remains, others get filtered out. Also, the difference is not calculated dynamically. Please help.

[Krishnakali]

@v-yuezhe-msft please help

[Krishnakali]

Hi,

Can someone please look into my query and help me. I am in dire need of a solution to this problem.

Thanks

[Krishnakali · Accepted solution]

I found a solution to this. Posting my solution here, in case anyone else is working on something similar. I created 2 date tables. Those tables have no relationship with any other tables. I created 2 sliders with these tables. Then I created measures by calculating sum of sales, filtered based on date=allselected(date filter 1). and date=allselected(date filter 2). This gives the results a single table.

[v-yuezhe-msft]

@Krishnakali,

Glad to hear the issue is solved, you can accept your reply answer, this way, other community members could benefit from your solution.

Regards,
Lydia