Forum Discussion
Helper IIIDischarge department
Community Championbdeleur
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
)
)
Helper IIII 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
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
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?
- tamerj1
HI bdeleur
I can understand when the total counts less. That is expected. If the same reference extends over the two months then it will be counted in both months while at the total level it will be counted once. Therefore, this is a non-additive calculation by nature like distinctcount. However, it is up to your business logic to determine whether to force additivty or not.
Don't be confused about the scenarios where the total counts more. Notice that this only happens when Feb returns blank value. Which means that some references were neither counted in Feb. nor in Mar. Apparently one record in Feb. and one record on Mar. so it will be counted in neither but the in the total level.To exclude same department movements just return to the very first solution.
- tamerj1
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
Hi, yes I understand that. I'll export my data and anomymize it.
- tamerj1
bdeleur
Initially the matrix analysis was based on "From" "To". Now I see a different matrix with "Months" in the columns of the matrix. For me this is a completely different analysis. Please clarify precisely what are you trying to accomplish.- bdeleur
Sorry for the confusion. With my short of knowledge I thought it was possible if you identifie the movements (matrix "from" to "to") you can count how many movements (unique reference) from a department to another, where you can filter the start department. So how many references are moved from A to B, to C etc and when you change the filter from B to A, to C etc. Displayed per date (month will do).
I gave you the data, is it helpfull?