Is there a way to allow a user to select between two dates?

[AliceW]

Hi community,

This is a tricky one and I could really use the help.

So, I have this Excel file where I have several lines per day. It's exported from Salesforce every day (hence, 'Date' is the export day) and show what Opportunities were present, their number, and their value. I already have about 20 days in.

E.g.:

Date	Initial / Final	Opp ID	Opp Value
01-05-19		111	100
01-05-19		222	200
01-05-19		333	300
02-05-19	Initial	222	200
02-05-19	Initial	333	300
02-05-19	Initial	444	400
03-05-19	Final	444	400
03-05-19	Final	555	500

Now, my users want to see what changes occured between two dates (hence the column 'Initial / Final'). This column is the one I manually change.

Consequently, I've developed a beautiful Power BI report with lots of cool measures and columns to show them just that. They all revolve around the 'Initial / Final' column.

At the moment, I have a pretty low tech solution: in the exported file, I manually add the word 'Initial' to one date and 'Final' to the other date. 

The said users, after the initial happiness with the report, would like something more. Namely, for them to be able to select, in the report, the 'Initial' and 'Final' dates, and thus allow them to compare different dates.

Is there a way to make this happen?

Thank you!

Alice

[v-danhe-msft]

Hi @AliceW ,

Have you tried to use a slicer to show different data in your table?

Regards,

Daniel He

[AliceW]

Hi Daniel,

My issue is that I want the users to be able to allocate 'Initial' to a given date (the same for 'Final').

Now, based on the manual addition in the Excel file, 'Initial' is 02-May. I'd love for the user to somehow click on a slicer, select a date, say 01-May, and then this date becomes the new 'Initial'.

Not sure if possible though.

[AnthonyTilley · Accepted solution]

this would depend on how all of your report is set up.

at the moment your Inital/final is a static colunm. however if you wish to make this dinamic it will need to be replaced with measure as all colunms are genarated at load so do not change based on slicers and filters.

i would create two calculated tables one for your inital date and one for your Final date.

(Go to modeling and click New table enter the formula below in the formula bar replace with your table and date field)

FINAL DATE = distinct(Table1[Date])

Repeat with

INITAL DATE = distinct(Table1[Date])

these two new tables can be used to create Slicers to select a single date for inital and a single date for final

then create a measure to apply a value of inital or final based on date

INITAL/FIANL =

Var i = SELECTEDVALUE('INITAL DATE'[Date],MIN('INITAL DATE'[Date]))

Var f = SELECTEDVALUE('FINAL DATE'[Date],MAX('FINAL DATE'[Date]))

Var d = MIN(Table1[Date])

return if(d=i,"INITAL",if(d=f,"FINAL",BLANK()))

Result is a measure that will move the inital of final value based on your selected results.

if more thatn one date is selected in one of the date slicers it will default to the min and max date.

[AliceW]

@AnthonyTilley 

Sorry for the late reply, as this were hectic at work.

Your solution works!!!!! THANK YOU SO MUCH!!!

Follow up question: do you know any good tutorials on variables?

[AnthonyTilley]

Sorry been on leave so havent been looking at posts 

i dont no any specifically for Veriables 

i subscribe to a you tube channel called Guy in a Cube they give some really good guides on power bi 

do a search in the channel for Dax and you will find loads of videos on dax veriables.