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.

timg's avatar
timg
Solution Sage
5 years ago

Hi,

Regarding your question about showing data between a start and end date, the following link explains how you can do this through slicers: https://radacad.com/from-and-to-date-slicers-in-power-bi-filtering-based-on-two-fields

Regarding your question about why a calendar table is recommended, there are several reasons, some of which are:

  • You can use it to join multiple tables together and analyze them together (e.g. you can filter all tables in your model with a single slicer
  • It reduces redundant data (e.g. you have 1x a column for date, year, month, quarter, etc. In your model Instead of having these columns in multiple tables.
  • It allows you to make calculations that require all days to be present in the dataset 
  • Etc.

Best regards,

Tim