Using 2 Date Fields as One Slicer

[dplank]

I have the data below. Currently the data is in 2 tables and 2 slicers for each table. If I add these fields to one table now and filter by closing month is August 2018, not all of the August 2018 funded loans will show because these all didn't close in August 2018.  How can I get the closing data and funding data into one table and create 1 slicer so when I click on August 2018 in the slicer it would show 6 closing units and 5 funding units in the same table? And then when I click on July 2018, it would use data from that month. Thank you for any help you can give!

[Anonymous · Accepted solution]

Hi @dplank

You can Append both table and make few changes in the model as below.

1.  Closure Table : Rename the closure date to "Date" and add new column "Category" with values "Closure"

Name	Units	Date	Category
X	XX	8/10/2018	Closure

2. FundingTable : Rename the Funding date to "Date" and add new column "Category" with values "Funding"

Name	Units	Date	Category
Y	YY	8/20/2018	Funding

3. Append both tables. All 3 steps should be done in Power Query.

4.Create slicer based on "Date" field , and now if you select 2018 Aug, you will get both Closing and Funding data.

Thanks

Raj

[dplank]

Hi @Anonymous

Thank you for the reply.  I am a little lost on your solution and what you mean by Power Query.  Sorry if that is a beginner thing.

I also failed to mention that I would need to do calculations off these fields in the same table.  For example, a basic calculation would be the units in closure table with dates in August plus the units in the funding table with dates in August (or any month that my slicer shows).  I would want to have this sum in one of the tables.  How would I get that?

Thank you!

[Anonymous]

Hi @dplank

In your desktop, go to Home- > Edit Queries , that will take you to Query Editor / Power Query.

Yes, if you follow the above steps you can do those calculations.

Thanks

Raj

[dplank]

Hi @Anonymous

I am still not able to get the results I need.  When I put the date field in the slicer, I have to choose either the date field from the closure table or from the funding table and then the results are filtered based off of that.  For instance, if I filter off of the date field from the closure table and select July 2018, the funding month will show results from July 2018 and August 2018 because the data is just filtered on loans that have a close date in July 2018.

[v-juanli-msft]

Hi @dplank

It is better for analysis if I know more details, such as the relationship between two tables, dataset structure, ect.

Best Regards

Maggie

[dplank]

I was able to find a solution already.  I created a date table and used that date column as my slicer and then created a measure taking the max of that date and used that measure in my formulas for the closing and funding columns.