Forum Discussion
Helper IIIYet Another Matrix Total Problem
jcawley That was the purpose of the IsWorkDay flag which you could add to your Date table or just change the DAX a bit to exclude holidays. Use EXCEPT. There wouldn't be so much back and forth on this if you had just described your situation fully the first time around.
Measure = VAR __Date = MINX( EXCEPT( FILTER( ALL('Dates'), [Date] >= TODAY() & [WorkDayFlag] = 1 ), 'Holidays' ),[Date] ) VAR __Result SUMX( SUMMARIZE( 'Table', [Team Member], "__Days", ( __Date - MAX('Table'[Start Date] ) ) * 1. ), [__Days] ) RETURN __Result
Community Championjcawley If you can provide sample data as text can be more specific.
First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8
Helper IIIThe Date table is just a date table. Just a list of calendar dates and nothing more. The team member table would look like:
Team Member Start Date
John Doe 04/01/2024
Jane Doe 03/01/2024
So if we subtracted TODAY() less these two start dates, we get 23 days and 54 days respectively. The correct total for this would be 77 days but what the measure would do is count 54+54 because of the MIN(). Which is incorrect!
- Greg_Deckler
jcawley No need for a date table then really. PBIX attached below sig.
Measure = SUMX( SUMMARIZE( 'Table', [Team Member], "__Days", ( TODAY() - MAX('Table'[Start Date] ) ) * 1. ), [__Days] )- jcawley
Thank you for the reply! I unfortunatly do need the date table - I do not want to count weekends or Holidays and I have a column in my date table that I've called 'Date'[Workday?] that accounts for those with a true/false. I was hoping if I simplified the problem I'd be able to tak on that last bit myself!
Any ideas on how to navigate around that last hurdle?- Greg_Deckler
jcawley Well, it's a simple change but I don't understand the logic you want as far as what date you actually want from the date table.
Measure = VAR __Date = MINX( FILTER( ALL('Dates'), [Date] >= TODAY() && [WorkDayFlag] = 1 ),[Date] ) VAR __Result SUMX( SUMMARIZE( 'Table', [Team Member], "__Days", ( __Date - MAX('Table'[Start Date] ) ) * 1. ), [__Days] ) RETURN __Result
- MNedix
Solution Sage
In your file, the same result can be achieved with this
Tenure = SUMX('Table',DATEDIFF('Table'[Start Date],TODAY(),DAY))I fully understand the logic behind your measure but could you please tell me if there is a "hidden" benefit when compared to mine? Also, what does the " *1. " do?
Best,
- Greg_Deckler
MNedix Using simple math tends to keep calculations in the storage engine versus the formula engine which tends to be faster. The * 1. just ensures that things get returned as a number.
- MNedix
jcawleyDo you insist of using the MIN function?
If I understood the problem correctly, then there could easily be two options:
1. create an additional column with a simple formula like:DateDiff = TODAY()-[Start Date]Then you simply add the DateDiff to a card (PowerBI should do the sum for you) or create a measure with SUM
2. create a measure as below:
Tenure = SUMX(Names,DATEDIFF(Names[Start Date],TODAY(),DAY))(assuming that 'Names' is your Team Members table)
If this solved your problem then please mark it as the solution.
Best,
