How to get the value of User input date through slicer

[Divya904]

Hi Experts

Please help me in this. How can i get the date value of User selected date through slicer and use this date value in my calculation.

Thanks,

Regards

Divya

[Anonymous]

The answer to this question will depend a lot on how you have set up your report and what you are attempting to do.  Based on your question as written, i think you might be looking for this formula

https://msdn.microsoft.com/en-us/query-bi/dax/selectedvalue-function

Alternatively, you could simply make use of the filter context and use LASTDATE passing it the column that you used in that slicer.  If a User has only only selected a single date, then LASTDATE should give you that date anyway

https://msdn.microsoft.com/en-us/query-bi/dax/lastdate-function-dax

[Divya904]

Hi Ross

Thanks for your reply.

1. I have on efact table which has Posting date

2. Created a DAX table for Calendar Date

3. Created relationship between Posting date and Calendar Date

4. Created a slicer on Calendar Date (dropdown type)

Requirement : When user select any single date in the slicer. I want that date should pass to followin Measure

Number of days = Posting Date - User Selected Date (the date selected by user in the slicer as mentioned above)

Thanks for your help.

Regards

[Anonymous]

Yep so i'd suggest using LASTDATE('CalendarTable'[DateField]).  Because you have used this in the slicer, when the measure is run it will check the calendar table.  The slicer will have filtered the entire table down to just a single row.  Therefore when it looks for the Last Date, it will simply get only that one result, and that is what will be returned.

Of course when they select nothing, you will get the last date in the table, but we could add an If statement to account for this.  You could do an if that does:

IF( 
	HASONEVALUE ('CalendarTable'[DateField]), 
	<Your Measure>, 
	<Some Value for when nothing is selected>
)

[Divya904]

Hi Ross

Really appreciate your quick response. But seems LASTDATE() is not working for me. To give you more details about the requirement, I have attached some screenshots. Hope these will help you to understand the issue.

1. Firstly, i have created a DAX table for Date. Using this date i have created a Date Slicer. Whatever the date user select in this 'Calendar Date' slicer it should pass to my measure calculation. That is detailed in screen shot 2.

2. This screenshot captures the measure calculation for difference between Fact table that (posting date) and Calendar Date (User input date through slicer)The result of the measure is being used to drive these aging calculations.

1234

Thanks again for your time.

[Anonymous]

Is the Age Trial Balance tabled linked in such a way that the sliced date could be adversely hiding the data you need from that table?

If so, we might need to run a calculate statement with some type of ALL to cause it to ignore some or all of the slicer context.

[Divya904]

Sliced date is not hiding the data as such. It is calculating the date based on user selection date. (If you wants to see what was the amount in which bracket in past particular date)

All the brackets like Age 1-30, Age 30-60 are Calculated as below . When the User selects a date, these brackets should adjust as that. 

Age 1 - 30 = if([Posting date - User entered date]< 0, CALCULATE ( Sum('Age Trial Balance'[Amount In Local Currency With +/- signs]),  FILTER ('Age Trial Balance',[WhatIsTheAge] >= 1 && [WhatIsTheAge] < 31 ) ,FILTER('Age Trial Balance',[Item Status] = "O")),0)

Basically the functionality in Power BI to filter the data so that the report shows us data from a previous date in time e.g. 31.03.2018.

Thanks

[Anonymous]

Ok, how about this.  Create a new Calendar table, which is completely disconnected from your Table Relationships.  Its job is to simply act as your DateSelector.

Your Age Range formulas can be optimised to look like this:

Age 1 - 30 = IF(
	[Posting date - User entered date] < 0, 
	CALCULATE (
		Sum('Age Trial Balance'[Amount In Local Currency With +/- signs]
		'Age Trial Balance'[WhatIsTheAge] >= 1, 
		'Age Trial Balance'[WhatIsTheAge] < 31
		'Age Trial Balance'[Item Status] = "O"
	),
	0
)

And your other measure could be updated to look like 

DifferenceOfPostingAndInputDate = DATEDIFF(
	FIRSTDATE('Age Trial Balance'[Posting Date In The Document]),
	FIRSTDATE('DateSelector'[Date])
)

[Divya904]

Hi Ross

When i remove the relationship between DAX Calendar date Table and my Fact table . Then i get the following error in the calculation. I guess it won't work without the relationship. Please advise. Thanks

[Anonymous]

@Divya904My suggestion would actually be to create a 2nd Date Table, rather than disconnect the existing one.  Because its for date selection, i'd recommend doing this in Edit Queries and performing the following:

1. Open Edit Queries
2. Find your Age Trial Balance Table
3. Right Click and Select Reference
4. Name the new Query "DateSelection"
5. Find the Date Column and select the option "Remove Other Columns"
6. Remove Duplicates
7. Sort

When you close and apply, make sure this table is not included in your table relationships.  Use this table in your slicer.

[Divya904]

Hi Ross

Thanks for your detailed response. Appreciate it.

I did exactly what you advised, but still not able to get the desired output.

[Anonymous]

Oh I see the problem.  You have missed a bracket at the end of the FIRSTDATE.  Power BI will have attempted to add the bracket in, but will have added it to the end.  Make sure to space your DAX code out so its easier to debug 😉

Here is what it should look like (check for typos):

DifferenceOfPostingAndInputDate = DATEDIFF(
	FIRSTDATE('Age Trial Balance'[Posting Date In The Document]),
	FIRSTDATE('DateSelection'[SelectDate]),
	DAY
)

[Divya904]

Hi Ross

Sorry, that was my mistake. The error is different here, 

Thanks

[Anonymous]

Looking at the new error and your Table/Field list on the far right. You haven't created the date selection table yet?

[Divya904]

Hi Ross

I have created the DateSelection table in query editor as you advised. I can see it in my far right listing.

Many Thanks

[Anonymous]

Great, yes i see it in the new screenshot, so now you need to look at the Error from your previous screenshot and see that it was looking for information from that table (that was yet to exist).  If its still showing the same error now, you might need to tweak the code to line up with the naming convention you have used for the table and field.

[Divya904]

Hi Ross

Below screenshot will tell show you tables and name.

Measure calculation is giving the same error. Cannot find the table DateSelection'

DifferenceOfPostingAndInputDate = DATEDIFF(
FIRSTDATE('Age Trial Balance'[Posting Date In The Document]),
FIRSTDATE('DateSelection'[SelectDate]),
DAY
)

[Anonymous]

The table and field names look like that match the formula, although there is a symbol next to your SelectDate field that looks like its a measure?  I would have expected that the Date field would simply be a standard datacolumn and wouldn't have an icon.

[Divya904]

'SelectDate' column in DateSelection table, is of Date type. That symbol is date datatype symbol. It is not a measure.

Thanks

[Anonymous]

Ok glad we got that cleared up.  Can you please click on the measure or error and see if you are getting the same or a new error?

[Divya904]

Hi Ross

Yes, i am getting the same error. "Cannot find the table 'DataSelection" ,

From my past experience, i always see this error when we are trying to use columns from other table. However i tried using RELATED as well. But did not work.

😞