Dynamic Date Periods in Power BI Table

Hi manoj_0911 ,

 

I’d like to acknowledge the valuable input provided by danextian . His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.

In my investigation, I took the following steps:

 

I regret to inform you that I did not see any example data, so I created the following example data myself:

IXN_SUBHOUR_DATE
1/1/2024 8:00:00 AM
1/2/2024 11:00:00 PM
1/3/2024 12:00:00 PM
1/3/2024 6:00:00 PM
1/4/2024 7:00:00 AM
1/2/2024 6:00:00 AM
1/4/2024 2:00:00 PM
1/5/2024 6:00:00 AM
1/6/2024 8:00:00 AM
1/7/2024 3:00:00 PM
1/8/2024 8:00:00 PM
1/9/2024 1:00:00 PM

 

I have two different ways to achieve your needs, this is the first one:

 

Create different calculated columns according to the different date ranges you want to display, as follows:

Hour = 'Table'[IXN_SUBHOUR_DATE].[Month] & " " & 'Table'[IXN_SUBHOUR_DATE].[Day] & " " & 'Table'[IXN_SUBHOUR_DATE].[Year] & " " & TIME(HOUR('Table'[IXN_SUBHOUR_DATE]),MINUTE('Table'[IXN_SUBHOUR_DATE]),SECOND('Table'[IXN_SUBHOUR_DATE]))

Month = 'Table'[IXN_SUBHOUR_DATE].[Month] & "-" & YEAR('Table'[IXN_SUBHOUR_DATE])

Sub-Hour = 'Table'[IXN_SUBHOUR_DATE].[Month] & " " & 'Table'[IXN_SUBHOUR_DATE].[Day] & " " & 'Table'[IXN_SUBHOUR_DATE].[Year] & " " & TIME(HOUR('Table'[IXN_SUBHOUR_DATE]),MINUTE('Table'[IXN_SUBHOUR_DATE]),SECOND('Table'[IXN_SUBHOUR_DATE]))

Day = MONTH('Table'[IXN_SUBHOUR_DATE]) & "/" & DAY('Table'[IXN_SUBHOUR_DATE]) & "/" & YEAR('Table'[IXN_SUBHOUR_DATE])

 

I don't see any difference between the display formats of Sub-Hour and Hour from your requirements.

 

Create a field parameter and select the created calculated column. Remember to check the Slicer option.

 

The effect of selecting pages from different periods is as follows:

 

Another way is:

 

First create a disconnected table containing all the time periods to be selected.

Period
Hour
Sub-Hour
Month
Day

 

Create a measure:

Measure = 
VAR SelectedPeriod = SELECTEDVALUE('DatePeriods'[Period], "Day")
RETURN
SWITCH(
    SelectedPeriod,
    "Sub-Hour", FORMAT(MAX('Table'[IXN_SUBHOUR_DATE]), "mmm d yyyy hh:mmAM/PM"),
    "Hour", FORMAT(MAX('Table'[IXN_SUBHOUR_DATE]), "mmm d yyyy hh:mmAM/PM"),
    "Day", FORMAT(MAX('Table'[IXN_SUBHOUR_DATE]), "M/D/YYYY"),
    "Month", FORMAT(MAX('Table'[IXN_SUBHOUR_DATE]), "mmm-yy"),
    FORMAT(MAX('Table'[IXN_SUBHOUR_DATE]), "M/D/YYYY")
)

 

The final page effect is shown below:

 

Overall, the second method performs better and is more convenient.

 

The first and third lines are the time taken by the second method, and the other two lines are the time taken by the first line.

 

Please choose your preferred method according to your needs.

 

pbix file is attached.

 

If you have any further questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!