Forum Discussion

fleming507's avatar
fleming507
Frequent Visitor
8 years ago
Solved

Number of Days Value to Rows

 I have a dataset that I need to maniuplate for year end reporting, but the data from source is not entirely suitable. For each service there is a Scheduled Start Date and Numbder of days scheduled. ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi fleming507

     

    Try this calculated table. Assuming your table name is Table1

     

    Table =
VAR Temp1 =
    GENERATE (
        Table1,
        VAR mydays =
            CALCULATE (
                VALUES ( Table1[ NumberOfDays] ),
                Table1[ServiceName] = EARLIER ( Table1[ServiceName] )
            )
        VAR adddays =
            (
                INT ( mydays / 7 )
                    + 1
            )
                * 2
        RETURN
            GENERATESERIES ( 1, mydays + adddays )
    )
VAR temp2 =
    ADDCOLUMNS (
        ADDCOLUMNS ( Temp1, "Scheduled Dates", [ScheduledStartDate ] + [Value] - 1 ),
        "WeekDay", WEEKDAY ( [Scheduled Dates], 2 )
    )
VAR temp3 =
    FILTER ( temp2, [WeekDay] < 6 )
VAR temp4 =
    ADDCOLUMNS (
        temp3,
        "RANK", RANKX (
            FILTER ( temp3, [ServiceName] = EARLIER ( [ServiceName] ) ),
            [Scheduled Dates],
            ,
            ASC,
            DENSE
        )
    )
VAR temp5 =
    FILTER ( temp4, [RANK] <= [ NumberOfDays] )
RETURN
    SUMMARIZE ( temp5, [ServiceName], [Scheduled Dates] )
fleming507's avatar
fleming507
Frequent Visitor
8 years ago

Thanks zubair, on further review my data was erronous. once corrected I achieved the desired reults with the original solution.