cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rashamohsen
Frequent Visitor

Date filters

Hallow everyone

 

I have a problem with my date table, I created two Start date and End date, and they have four fields: Date, Day, Month and Year.

I need to use two filter dates like Start date and End date to filter another table that has two date fileds: Created date and closed date.

What I need is to let the user choose a range of time what ever it is between start and end date.

The user should set the Start date and the End date which are comming from the created Date tables.

I can not use the date fileds from my table, because I want to use these dates like variables in dax for some estimating.

for  that I created a  new column in my table using DAX like:

 

my columns = Var EskBeginn = ('my table'[CreatedDate]) 
Var EskEnde = ('my table'[ClosedDate]) 
Var StartDtum = SELECTEDVALUE('StartDate'[Date])
Var EndDatum = SELECTEDVALUE('EndDate'[Date])
return IF(EskBeginn < StartDtum && EskEnde > StartDtum && EskEnde < EndDatum, 1, IF(EskBeginn > StartDtum && EskBeginn < EndDatum && EskEnde > EndDatum, -1, 0))

 

 

 
 
return IF(EskBeginn < StartDtum && EskEnde > StartDtum && EskEnde < EndDatum, 1,
IF(EskBeginn > StartDtum && EskBeginn < EndDatum && EskEnde > EndDatum, -1, 0))
And the problem is that I can not use StartDate and End date(the created date tables) in Dax,
beacuse it includes many values where Power Bi expects only one value(says power bi).
I tried selectedvalue('StartDate'[Date]), it does not work , it is always empty.
I tried to make a relationship between Start date and my table, it did not work, like that I can not use the Start and End date
slicer, when I use them the entire report will be empty.
I knew that we can not use a refrence filter or parameter for the end use.
Example: to filter created date and closed date with a refrence in the sql, and then make the user give refernce value
in the parameter.
The parameters are only for the users who have power bi desktop, and I do not want to let them have original report.
It would be nice to answer my quistion if anyone know the answer.
 
Best regards
 

 

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@rashamohsen 

Can you explain it with a sample pbix if possible, which helps us to understand your requirements. 

Based on the current information, can't you just create a slicer use the date column.

 

date slilcer.JPG

 

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

 

 

HI

 

I am sorry, I tried to send a screenshot but it does not let me send it, I could not load it and I could not copy it.

 
 
 

Hallo

Thanks for your reply, but I have a slicer (from my Date tables) like you mentioned, but my problem is that I do not know how to connect or to bind my Date tables with my Customer Table.

The report is about customer satisfaction.

I need tow Dates Start and edn date where the user can choose randolmy any date.

For my estimation I created a column incident, in this column I have to compare if the incident-Createddate  greater or less than the choosed Start date and the same for closed date and End date, to give incident a value from (-1,0 or 1).

But I can not use the Start and End date from my date tables, the DAX can't see them, it assumes that Start date has multiple values where it expects one value. I defined four variables like this:

 

 

Incident = Var EskBeginn = ('Incident'[System_CreatedDate])                                                                                        Var EskEnde = ('Incident'[Microsoft_VSTS_Common_ClosedDate])                                                                                        Var StartDtum = SELECTEDVALUE('StartDate'[Date])
Var EndDatum = SELECTEDVALUE('EndDate'[Date])
return IF(EskBeginn < StartDtum && EskEnde > StartDtum && EskEnde < EndDatum, 1, IF(EskBeginn > StartDtum && EskBeginn < EndDatum  && EskEnde > EndDatum, -1, 0))

 

 

selectedvalue did not help, and I can not make a relationship between the three tables.

 
 
 
 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors