cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AliceW Member
Member

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

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.:

DateInitial / FinalOpp IDOpp Value
01-05-19 111100
01-05-19 222200
01-05-19 333300
02-05-19Initial222200
02-05-19Initial333300
02-05-19Initial444400
03-05-19Final444400
03-05-19Final555500

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
AnthonyTilley Established Member
Established Member

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

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.
 
 

View solution in original post

5 REPLIES 5
Microsoft v-danhe-msft
Microsoft

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

Hi @AliceW ,

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

1.PNG

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AliceW Member
Member

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

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 Established Member
Established Member

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

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.
 
 

View solution in original post

AliceW Member
Member

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

@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 Established Member
Established Member

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

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.

 

 

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)