Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like to count a total number of available days between a variable range of two dates defined by a slicer. I have a table with employees ID, start date and end date. Missing end dates indicate employees are still employed.
In my report I would like to have a card visual that shows the sum of days that employees are available between a date range defined by a slicer. Something like this:
Is it possible to create such a measure?
Thank you for your help!
Solved! Go to Solution.
Hi @Farmertree ,
Yes, it is possible. You can add variables to extract min and max date from selected calendar date and compare with start/end date to get datediff. After these, package them with a sumx function to summary datediff result.
Meausre = VAR selected = ALLSELECTED ( Calendar[Date] ) VAR _max = MAXX ( selected, [Date] ) VAR _min = MINX ( selected, [Date] ) RETURN SUMX ( ADDCOLUMNS ( ALLSELECTED ( Table ), "Diff", DATEDIFF ( MAX ( [Start date], _min ), MIN ( [End date], _max ), DAY ) ), [Diff] )
Regards,
Xiaoxin Sheng
@v-shex-msft Thanks! This helps a lot. The only problem is that this formula returns a negative DATEDIFF value for End dates < _min. Do you know how i could apply a filter within this formula that only selects rows in which Enddate >= _min?
Hi @Farmertree ,
Yes, it is possible. You can add variables to extract min and max date from selected calendar date and compare with start/end date to get datediff. After these, package them with a sumx function to summary datediff result.
Meausre = VAR selected = ALLSELECTED ( Calendar[Date] ) VAR _max = MAXX ( selected, [Date] ) VAR _min = MINX ( selected, [Date] ) RETURN SUMX ( ADDCOLUMNS ( ALLSELECTED ( Table ), "Diff", DATEDIFF ( MAX ( [Start date], _min ), MIN ( [End date], _max ), DAY ) ), [Diff] )
Regards,
Xiaoxin Sheng
@v-shex-msft Thanks! This helps a lot. The only problem is that this formula returns a negative DATEDIFF value for End dates < _min. Do you know how i could apply a filter within this formula that only selects rows in which Enddate >= _min?
HI @Farmertree ,
You can do compare between date and _min to get the bigger one and use in datediff function:
Measure = VAR selected = ALLSELECTED ( Calendar[Date] ) VAR _max = MAXX ( selected, [Date] ) VAR _min = MINX ( selected, [Date] ) RETURN SUMX ( ADDCOLUMNS ( ALLSELECTED ( Table ), "Diff", DATEDIFF ( MAX ( [Start date], _min ), MIN ( MAX ( [End date], _min ), _max ), DAY ) ), [Diff] )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |