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] )
v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi fleming507,

 

The answer of Zubair_Muhammad should solve your problem.

 

If you have solved your problem, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

Best Regards,

Cherrry