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.

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.

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

        Hi tamerj1 ,

         

        Sorry for the delay. It was a bussy week. 

        The count seams to work.

        I have a question still: the totals give a differnt count the the sum in the matrix.

        And I want to skip the count the movement between the same department, it isn't relevant.

        Can you help me to understand it?

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

      tamerj1I know you're very busy but please don't forget me 🙂

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

      bdeleur 
      Sorry I didn't notice your reply. In order to provide your with accurate solution I need a sample dummy data (large just enough to provide significant output) along with the expected results. You can do that in excel or whatever tool you prefer. A sample PBIX file would also be great.

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

        Hi, yes I understand that. I'll export my data and anomymize it.