Forum Discussion

MrMike's avatar
MrMike
Helper II
5 years ago
Solved

Rows between two dates

In Power BI desktop, how to show rows that are between a start date and end date? Also which visualation to use? Is slicer the best option?   For example, my data is like this: Start Date End ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  MrMike  ,

    Here are the steps you can follow：

    1. Create measure.

    Flag1 =
var _selectedperiod=
SELECTCOLUMNS(ALLSELECTED('date'),"Date", [Date])
return
IF(MAX('Table'[Start Date]) in _selectedperiod||MAX('Table'[End Date]) in _selectedperiod||
(MAX('Table'[Start Date])<=MIN('date'[Date])&&MAX('Table'[End Date])>=MAX('date'[Date]))
,1,0)

    2. Result

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi  MrMike ,

Here are the steps you can follow：

1. Created a calendar table using calculated table

date = CALENDARAUTO( )

2. Create measure.

Flag1 =
var _selectedperiod=
SELECTCOLUMNS(ALLSELECTED('date'),"Date", [Date])
return
IF(MAX('Table'[Start Date]) in _selectedperiod||MAX('Table'[End Date]) in _selectedperiod,1,0)

3. Set Flag1 is 1. in Filter.

4. Result：

 

You can downloaded PBIX file from here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

MrMike's avatar
MrMike
Helper II
5 years ago

Still problem. I think we are close to solving this. The DAX needs be like Start Date is equal to or greater than the selected date range AND End Date is equal to or less than the selected date range. How do I code that?

Something like 

 

Flag1 =
var DateRange = SELECTCOLUMNS(ALLSELECTED('date'),"Date", [Date])
return
IF(MAX('Table'[Start Date]) >= DateRange & MAX('Table'[End Date]) <= DateRange, 1, 0)
 
but Power BI gives me error becuase my synax is wrong, so what is the right way?