Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Ajay Kamath]

Hi Guys,

I have a Date field and customer wants below to be done.

Display current date to next 5 days and then display the remaining in terms of weeks as below.. is there a way or any other approach available to implement this feature. Please suggest

——————————
Ajay Kamath

——————————

[Steve James]

Assuming you have a valid Date table with a column called Date, add the following column to the Date table and use it in the column field of your pivot table:

Date Band =
VAR _Today =
    TODAY ()
VAR _RelDate =
    INT ( 'Date'[Date] - _Today ) + 2
VAR _RelWeek =
    TRUNC ( DIVIDE ( _RelDate, 7 ), 0 )
VAR _RelDateDay =
    FORMAT ( 'Date'[Date], "mm" ) & "/"
        & FORMAT ( 'Date'[Date], "dd" ) & "/"
        & YEAR ( 'Date'[Date] )
VAR _RelWeekStart =
    FORMAT ( _Today + ( _RelWeek - 1 ) * 7 + 5, "mm/dd/yyyy" )
VAR _RelWeekEnd =
    FORMAT ( _Today + ( _RelWeek - 1 ) * 7 + 11, "mm/dd/yyyy" )
VAR _RelDateWeek = _RelWeekStart & " - " & _RelWeekEnd
VAR _Label =
    IF ( _RelWeek = 0, _RelDateDay, _RelDateWeek )
RETURN
    _Label
?

——————————
Steve James
Director
——————————
——————————————-

[Ajay Kamath]

Hi Steve,

Thanks much for your reply..i almost got it working in my report using your formula today but ended up getting a different requirement from client.

it has to be Display current date to next 5 days and also Previous 5 days  and then display the remaining in terms of weeks as below.. 

Can u please help on this too.

Thanks again

——————————
Ajay Kamath
T5J 3M5
——————————
——————————————-

[Steve James]

Hi Ajay

I created a temp Power BI desktop file to work up my solution, but I’m afraid I’ve deleted that now. However, you could experiment with the above column definition to see whether you can achieve what you want.

Firstly, the definition of _Label tests whether _RelWeek is 0. If you need to report at a day level for the previous week as well, that would have a _RelWeek of -1, so you could amend that part of the formula to test for 0 or -1. Secondly, the definition of _RelDate adds 2 to the current date to account for the fact that your first week (_RelWeek = 0) contains 5 days rather than 7. You may need to amend this, or duplicate the logic for dates in the past.

Bearing this in mind, you might want to try something like the following. I can’t guarantee that this will work though, as I haven’t been able to test it:

Date Band =
VAR _Today =
    TODAY ()
VAR _RelDate =
    IF (
        'Date'[Date] >= _Today,
        INT ( 'Date'[Date] - _Today ) + 2,
        INT ( 'Date'[Date] - _Today ) - 2
    )
VAR _RelWeek =
    TRUNC ( DIVIDE ( _RelDate, 7 ), 0 )
VAR _RelDateDay =
    FORMAT ( 'Date'[Date], "mm" ) & "/"
        & FORMAT ( 'Date'[Date], "dd" ) & "/"
        & YEAR ( 'Date'[Date] )
VAR _RelWeekStart =
    FORMAT ( _Today + ( _RelWeek - 1 ) * 7 + 5, "mm/dd/yyyy" )
VAR _RelWeekEnd =
    FORMAT ( _Today + ( _RelWeek - 1 ) * 7 + 11, "mm/dd/yyyy" )
VAR _RelDateWeek = _RelWeekStart & " - " & _RelWeekEnd
VAR _Label =
    IF ( _RelWeek = 0 || _RelWeek = -1, _RelDateDay, _RelDateWeek )
RETURN
    _Label
?

You’ll probably need to experiment and debug the code above. It’s sometimes useful to replace the return value (currently _Label) with intermediate results to check whether they are evaluating as expected.

——————————
Steve James
Director
——————————
——————————————-