Forum Discussion

User5231's avatar
User5231
Icon for Helper II rankHelper II
4 years ago
Solved

Evenly Assign Dates

I am looking for a solution for a problem say I have a list of 31 items as below, and I know what I want my Start and End Date to be. How can I auto assign an "even" distribution of dates with index 1 starting at 4/11 and index 31 ending on 4/30. Ideally acounting for working days only.  I would be intrested in an excel, or Dax solution here. 

 

Index 
14/11/2022
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
314/30/2022

 

Below is a table of how many times you would see the date repeat..

..

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi User5231 ,

    Please try this dax:

     

     

    Table =
VAR _StartDate =
    DATE ( 2022, 4, 11 )
VAR _EndDate =
    DATE ( 2022, 4, 30 )
VAR _WorkingDay =
    ADDCOLUMNS (
        FILTER (
            CALENDAR ( _StartDate, _EndDate ),
            WEEKDAY ( [Date], 2 ) < 6
                && [Date] <> DATE ( 2022, 4, 15 )
        ),
        "Number of Dates", IF ( WEEKDAY ( [Date], 2 ) = 3, 3, 2 )
    )
VAR _f =
    FILTER (
        CROSSJOIN ( GENERATESERIES ( 1, 3 ), _WorkingDay ),
        [Value] <= [Number of Dates]
    )
RETURN
    GROUPBY (
        ADDCOLUMNS (
            _f,
            "index",
                RANKX(_f,0.1*[Value]+VALUE([Date]),,ASC))
        ),
        [index],
        [Date]
    )

     

     

    Result:

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi User5231 ,

    Please try this dax:

     

     

    Table =
VAR _StartDate =
    DATE ( 2022, 4, 11 )
VAR _EndDate =
    DATE ( 2022, 4, 30 )
VAR _WorkingDay =
    ADDCOLUMNS (
        FILTER (
            CALENDAR ( _StartDate, _EndDate ),
            WEEKDAY ( [Date], 2 ) < 6
                && [Date] <> DATE ( 2022, 4, 15 )
        ),
        "Number of Dates", IF ( WEEKDAY ( [Date], 2 ) = 3, 3, 2 )
    )
VAR _f =
    FILTER (
        CROSSJOIN ( GENERATESERIES ( 1, 3 ), _WorkingDay ),
        [Value] <= [Number of Dates]
    )
RETURN
    GROUPBY (
        ADDCOLUMNS (
            _f,
            "index",
                RANKX(_f,0.1*[Value]+VALUE([Date]),,ASC))
        ),
        [index],
        [Date]
    )

     

     

    Result:

     

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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