Forum Discussion

bdeleur's avatar
bdeleur
Icon for Helper III rankHelper III
3 years ago
Solved

Discharge department

Hi,   I want to make a report witch contains the following information. Out of a big data file I want to extract the follow up department. So I have department A and how many have a transfer (dis...
Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    bdeleur 
    For now this is what I can provide you. The difference between Count 2 and Count 3 is that count 3 shall not count discharged from same department unless there is a discharge date. The difference shall not be noticed using the sample data as there are no blanks in the end date.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

bdeleur 
Please try

Count =
SUMX (
    VALUES ( 'Table'[Reference] ),
    VAR CurrentTable =
        CALCULATETABLE ( 'Table' )
    VAR FirstRecord =
        TOPN ( 1, CurrentTable, 'Table'[Start date], ASC )
    VAR LastRecord =
        TOPN ( 1, CurrentTable, 'Table'[Start date] )
    VAR FirstDepartment =
        MAXX ( FirstRecord, 'Table'[Department] )
    VAR LastDepartment =
        MAXX ( LastRecord, 'Table'[Department] )
    VAR DischargeDate =
        MAXX ( LastRecord, 'Table'[End Date] )
    RETURN
        IF (
            AND ( FirstDepartment = LastDepartment, DischargeDate <> BLANK () )
                && MAX ( 'To'[Department] ) = LastDepartment
                && MAX ( 'FROM'[Department] ) = FirstDepartment,
            1
        )
)
bdeleur's avatar
bdeleur
Icon for Helper III rankHelper III
3 years ago

Now the matrix stays empty when I apply the code in your file.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    bdeleur 
    I guess you are right. Sorry did not test. Pleas try

    Count =
SUMX (
    VALUES ( 'Table'[Reference] ),
    VAR CurrentTable =
        CALCULATETABLE ( 'Table' )
    VAR FirstRecord =
        TOPN ( 1, CurrentTable, 'Table'[Start date], ASC )
    VAR LastRecord =
        TOPN ( 1, CurrentTable, 'Table'[Start date] )
    VAR FirstDepartment =
        MAXX ( FirstRecord, 'Table'[Department] )
    VAR LastDepartment =
        MAXX ( LastRecord, 'Table'[Department] )
    VAR DischargeDate =
        MAXX ( LastRecord, 'Table'[End Date] )
    RETURN
        IF (
            OR (
                FirstDepartment = LastDepartment
                    && DischargeDate <> BLANK (),
                MAX ( 'To'[Department] ) = LastDepartment
                    && MAX ( 'FROM'[Department] ) = FirstDepartment
            ),
            1
        )
)
    • bdeleur's avatar
      bdeleur
      Icon for Helper III rankHelper III
      3 years ago

      I get some information now. But I'm trying to understand what I see.

      I see the movements for every department but I don't see the next department.

      I think I didn't meanson that well enough, sorry for that. And I like to see it like this:

       

      This is when I selected/slice or filter department A

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        bdeleur 
        For now this is what I can provide you. The difference between Count 2 and Count 3 is that count 3 shall not count discharged from same department unless there is a discharge date. The difference shall not be noticed using the sample data as there are no blanks in the end date.