Single slicer but multiple filters

[Anonymous]

Hi, 

I am new to PowerBI. Need some help on how to achieve the below result. 

I have my sales data which contains Order Date, Processed Date and Order Amount.  I have created 2 Calender tables and linked Order Date and Processed Date. 

Now I have want to create a single slicer with which I will be able to select the Year and Month and it filters the Order Data and Processed Date of that Year and Month and gives me the sales amount. 

Example Data:

Order Date	Processed Date	Sales Amount
1/01/2020	1/03/2020	5000
1/01/2020	13/01/2020	2000
1/01/2021	14/01/2021	3000
13/01/2021	14/01/2021	1000

Output:

If I select 2020 Jan -> 2000 (Filter on 2020 Jan Order Date and 2020 Jan on Processed Date) 

2021 Jan -> 4000

Please let me know how I can do this. 

Thanks in Advance.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a measure as follows.

Measure =

var x1=SELECTEDVALUE('calendar'[Date].[Year])

var x2=SELECTEDVALUE('calendar'[Date].[Month])

return

SUMX(FILTER(ALL('Sales'),[Order Date].[Year]=x1&&[Order Date].[Month]=x2&&[Processed Date].[Year]=x1&&[Processed Date].[Month]=x2),[Sales Amount])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Ashish_Mathur ,

Thank you for the file. 

Is there any way that I cannot alter the current table and convert coloumns into rows ( Attribute in your BI) . I have a lot of formulas written based on these coloumns. 

[Ashish_Mathur]

You are welcome.  I don't think so.  If we want a Year/Month slicer, we will have to keep only 1 date column in the base data sheet.

You may also want to wait for others to contribute to this thread.  May be others come up with a better solution.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi @nandic 

Thank you for the video. I managed to use 1 calendar instead of two. 

Can you please help me on how to get the above result? I want to get the sales amount which is Ordered and Processed in the same month. 

Thanks in Advance.

[nandic]

Hi @Anonymous ,
Best practice is to have one Date table and then you can have multiple (1 active, multiple inactive) relationships.
After that you use DAX function USERELATIONSHIP to define which relationship to use.
Here is a nice video on this topic: https://www.youtube.com/watch?v=u8_aeLmXbVk 

Regards,
Nemanja Andic