Forum Discussion
Dax Measure for selecting a value in a specific column based on dates
Hello,
I'm having problems wrinting a measure in dax. I have a fact table "EmployeeMD" that has all the employees and the working positions that they now have or had in the past. Every position has a start and end date and the first startdate and the last enddate are also grouped to make the "from - to" date that the employee is active. If the employee is still working, the enddate = 31/12/9999. Every position has a FTE value that may or may not change.
For example Employee X has 5 different positions (rows) in the table. The "from-to" is the same for every line. The FTE value changes over time. The position start and enddate give the periode the person was active on that position.
I also have a dates table with dates ranging from 01/01/2020 untill 31/12/2023.
The data is visible in the following matrix:
This is the measure I currently have:
This measure works on the lowest level and looks in what period of a position the month and year is and takes the sum of the FTE values that corresponds. But when I collapse the matrix, the sum of all the 5 FTE values is made, resulting in:
I figured this is because the dates on this level that are used in the calculation are the "from to" dates and every line in the table is in this range, resulting in the sum of all FTE values.
How can I rewrite the measure to have the count of a single fte value that corresponds with the month and year when the matrix is collapsed?
Thanks in advance!
Hi , AnonymousUser
According to your description , you want to to have the count of a single fte value that corresponds with the month and year when the matrix is collapsed.
I test in my side , here are the steps you can refer to :
(1)This is my test data :
(2)I create a measure as the same as yours, the result is as follows:
(3)For your need , we can just create another measure based on the orginal measure you created:
Measure = SUMX( ADDCOLUMNS( CROSSJOIN( VALUES('EmployeeMD'[PositionStartdate]) ,VALUES('EmployeeMD'[PositionEnddate])) ,"V" , [FTECount]) , [V])Then we can meet your need , the result is as follows:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
- v-yueyunzh-msft
Community Support
Hi , AnonymousUser
According to your description , you want to to have the count of a single fte value that corresponds with the month and year when the matrix is collapsed.
I test in my side , here are the steps you can refer to :
(1)This is my test data :
(2)I create a measure as the same as yours, the result is as follows:
(3)For your need , we can just create another measure based on the orginal measure you created:
Measure = SUMX( ADDCOLUMNS( CROSSJOIN( VALUES('EmployeeMD'[PositionStartdate]) ,VALUES('EmployeeMD'[PositionEnddate])) ,"V" , [FTECount]) , [V])Then we can meet your need , the result is as follows:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
- AnonymousUserFrequent Visitor
Hi,
This helped a lot, thank you!