Re: Multiple Months/Dates - Using Three Slicers

TheSAY · ‎01-18-2019

Good afternoon,

I am looking for some help to get a report changed for better utilization of the data, but I am currently struggling on how to do so.

We have a Membership Information report that is currently setup as follows:

A folder called Units holds CSV spreadsheets. They are titled as the year the data is for and contain data for December of that year. For example, the 2012.csv spreadsheet contains data from December 2012.
When the data is run, the "Report Run Date" data is the first of the new year. For example, the 2012.csv spreadsheet has 01/01/2013 for the Report Run Date column.
Data queries were created for each separate CSV file. For example, a query called "Members 2012" was created for the 2012.csv file.
Each query was edited to remove certain columns and add custom/conditional columns to differentiate the 2012 year from the 2013 year the report was run from.
A "Members" query is combining all of them.

Here's what the 2012.csv looks like:

Here's what the query looks like, after making the changes needed (we only needed the Branch, Membership Type, Report Run Date, Current Units and Current Members columns):

In the example above, the "Year" column was added to allow the conditional column of "Report Year" to show the actual year the data was for. 2013 > 2012, 2014 > 2013, and so on.

As of right now, the report uses "Branch" and "Membership Type" as the slicers.

What's I'm hoping to do, is be able to run ALL of the date ranges together, rather than just December, and use a single spreadsheet to allow "Branch", "Membership Type" and "Month" as the slicers, so we have a better comparison per year, rather than all prior years remaining the same.

Here's what the spreadsheet looks like with everything:

We would need the same thing to happen, where if the "Report Run Date" is September, the actual month would be August, and if the "Report Run Date" is January, the actual month would be December of the prior year.

Testing it out, I was able to duplicate the December numbers, but only if Month "1" was selected. Here's what it looks like:

As you can also see, it's only showing 2013 and not 2012 like the other one does.

Is anyone able to help me get this changed, so only one spreadsheet needs to be used, all years show up, and the three slicers can be used?

v-frfei-msft · ‎01-20-2019

Hi @TheSAY,

Maybe we can create an independent date table and add the new date table as slicer to work on it. We can use SELECTEDVALUE function to create measures to work on it. Kindly share your pbix and csv to me, please upload the files to One Drive and share the link here.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.