How to select the start and end date from a date slicer and use it at the power query

[rohithkothaneth]

Hello,

I have a requirement to generate report trends based on the dates (Begin & End dates) selected from a date slicer.

1.  Is there an option for me, if a user selects a START and END date using the date slicer. Could the same date be passed at the power query level to generate a table with additional dates columns like 12 Months date (based on End date), Prior 6 months dates etc 

2. I tried using DAX and yes was able to get the exact value passed here but I would end up creating multiple mesasures for different scenarios based on how i would want to trend different values.

For example in a report page, i will have trend data based on the Begin and End date values, also will need to generate another trend in the same page to go back to 12 months from the End date selected in the date slicer.

Could you please suggest options that will work for my situation?

Thanks,
Rohith 

[parry2k]

@rohithkothaneth if you really need help, you can do bit of home work and create a dummy data and share it.

[rohithkothaneth]

Thank you @parry2k for you prompt response. Unfortunately Im working on a clinet network and I have restriction in sharing the files over any drive.

I will try my best to explain my model here

1. I have a "Report" table with report type and number column, here we need to trend on count(number) based on the types available.  

2. "_Date" table generated via DAX looking at the minimum and max date availabel from the "Report" table. With that said there is an active relationship between these two table.

3. Date Slicer visual is been created on the begin and end date by looking at the "_Date" table [Date] column. Once the dates are been picked all the pages will get these dates synced.

4. My report has a bar chart that needs to generate data based on the dates selected in the date slicer. This is easily doable

5. Second trend chart which needs to look back for 12 months from the End date that is been selected in the date slicer.  

There are other pages that needs to be generated which involves looking at the date slicer date values and also needs to generate data looking abck 6 or 12 months from the End date value.

As a result, I was looking for option,  if there is a way if the dates that are been selected in the date slicer, could be used within the power query and create a table with the corresponding values like - begin month date, End Month date, 12 Month year begin, Quarter begin etc.

Or do you think a table using measures will be a better option for me here?

[parry2k]

@rohithkothaneth this can be easily achieve with the DAX measures, will be easier if you provide a sample pbix file, share using one drive/google drive and will get you the solution.

Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!