topic Re: Total seems correct but row values in table visual seem incorrect in DAX Commands and Tips

Total seems correct but row values in table visual seem incorrect

cathoms — Thu, 12 Oct 2023 20:45:34 GMT

I'm in health care and we have had to hire a lot of travel nurses to meet staffing demand. Travelers have much higher hourly pay than regular employees. I'm trying to calculate the incremental cost, (i.e. how much 'extra' travelers cost us) on a monthly basis in our current fiscal year (FY24, runs July-June). I have start and end dates for each traveler contract, weekly hours for each contract, the hourly pay rate difference, and I created a measure that calculates the current weeks worked in FY24 based on filter context.

Weekly hours * the pay rate difference is the weekly increment for each contract, which I calculate in the query editor as a new column called WeeklyIncrmt

WeeksToDate in FY24 is a measure:

WeeksToDate FY24 = VAR _Days = IF ( MAX(TravelerFactCurrentFY[Start FY]) = 2024, DATEDIFF ( MAX(TravelerFactCurrentFY[Start Date]), MIN( MAX(DateDim[DateValue]), MIN(MAX(TravelerFactCurrentFY[End Date]), DATE( 2024,6,30) ) ), DAY ), IF ( MAX(TravelerFactCurrentFY[Start FY]) < 2024 && MAX(TravelerFactCurrentFY[End Date]) > DATE ( 2023, 7, 1 ), DATEDIFF ( DATE ( 2023, 7, 1 ), MIN( MAX(DateDim[DateValue]), MAX(TravelerFactCurrentFY[End Date])), DAY ), 0 ) ) RETURN IF( _Days > 0, DIVIDE ( _Days, 7 ), BLANK() )

Incremental cost is the summed product of weekly increment * WeeksToDate in FY24. I have some date based filters to allow for filtering by year and month. Here is my DAX:

WeeklyIncrmtCost = CALCULATE( SUMX( TravelerFactCurrentFY, TravelerFactCurrentFY[WeeklyIncrmt]*TravelerFactCurrentFY[WeeksToDate FY24] ), FILTER( VALUES( TravelerFactCurrentFY[Start Date] ), TravelerFactCurrentFY[Start Date] <= MAX ( DateDim[DateValue] ) ), FILTER( VALUES(TravelerFactCurrentFY[End Date]), TravelerFactCurrentFY[End Date] >= MIN( DateDim[DateValue]) ) )

The problem that I am running into is sort of the opposite of the common "total is wrong" issue. In my case the total for my measure is pretty close to what I expect the value to be but when I put the measure in a table with year-month, the values in each row seem off and together total far more than the actual total.

I tried some of the solutions to the "incorrect total" but they don't work within my current measure. Any help would be much appreciated!

Re: Total seems correct but row values in table visual seem incorrect

Greg_Deckler — Thu, 12 Oct 2023 21:38:26 GMT

cathoms Sample data would help, but try this for your second measure. Has the added advantage that you can troubleshoot it by returning the VAR's and even the __Table VAR using TOCSV( __Table ).

WeeklyIncrmtCost = VAR __MaxDate = MAX( DateDim[DateValue] ) VAR __MinDate = MIN( DateDim[DateValue] ) VAR __Table = FILTER( TravelerFactCurrentFY, [Start Date] <= __MaxDate && [End Date] >= __MinDate ) VAR __Result = SUMX( __Table, [WeeklyIncrmt] * [WeeksToDate FY24] ) RETURN __Result

Re: Total seems correct but row values in table visual seem incorrect

cathoms — Fri, 13 Oct 2023 17:59:07 GMT

Thanks for the response. Unfortunately your measure performs exactly the same as mine. Note that I have inactive relationships between DateValue in my date dimension table to both Start Date and End Date. DateDim is pretty standard and has a YearMonth field for use in visualizations. I'm attaching some data pared down to essential columns. Hopefully that helps.

Index	Start Date	End Date	Start FY	Weekly Hour	WeeklyIncrmt
1001	1/19/2023	7/22/2023	2023	36	$738.00
1002	7/23/2023	10/21/2023	2024	36	$738.00
1003	5/22/2023	8/19/2023	2023	40	$820.00
1004	10/22/2023	1/18/2024	2024	36	$738.00
1005	7/16/2023	10/15/2023	2024	40	$879.60
1006	6/4/2023	9/2/2023	2023	40	$879.60
1007	5/28/2023	8/26/2023	2023	40	$879.60
1008	4/9/2023	7/29/2023	2023	40	$1,079.60
1009	8/20/2023	11/19/2023	2024	40	$879.60
1010	4/10/2023	7/8/2023	2023	40	$1,079.60
1011	7/23/2023	9/2/2023	2024	40	$879.60
1012	4/17/2023	7/15/2023	2023	40	$1,079.60
1013	10/16/2023	12/23/2023	2024	40	$879.60
1014	5/28/2023	8/26/2023	2023	48	$1,667.52
1015	5/28/2023	7/11/2023	2023	36	$1,250.64
1016	8/14/2023	11/11/2023	2024	40	$1,213.60
1017	4/17/2023	7/22/2023	2023	48	$1,295.52
1018	10/2/2023	12/30/2023	2024	32	$703.68
1019	4/18/2023	8/5/2023	2023	40	$1,079.60
1020	8/6/2023	11/4/2023	2024	40	$879.60
1021	11/5/2023	12/24/2023	2024	40	$879.60
1022	4/23/2023	7/14/2023	2023	36	$738.00
1023	11/6/2023	2/3/2024	2024	40	$1,389.60
1024	4/24/2023	7/22/2023	2023	40	$1,389.60
1025	9/5/2023	12/2/2023	2024	40	$879.60
1026	8/28/2023	11/26/2023	2024	48	$1,667.52
1027	4/30/2023	7/30/2023	2023	40	$1,079.60
1028	9/1/2023	10/14/2023	2024	40	$879.60
1029	5/1/2023	7/29/2023	2023	40	$1,079.60
1030	5/8/2023	8/5/2023	2023	40	$1,389.60
1031	8/6/2023	9/16/2023	2024	40	$1,389.60
1032	9/25/2023	12/23/2023	2024	40	$879.60
1033	9/3/2023	12/3/2023	2024	40	$879.60
1034	8/20/2023	11/18/2023	2024	36	$738.00
1035	10/30/2023	1/27/2024	2024	40	$879.60
1036	10/8/2023	1/6/2024	2024	40	$879.60
1037	11/6/2023	2/3/2024	2024	40	$820.00
1038	9/5/2023	9/29/2023	2024	40	$879.60
1039	2/6/2023	8/12/2023	2023	40	$3,020.00
1040	8/13/2023	10/20/2023	2024	40	$2,620.00
1041	4/19/2023	7/15/2023	2023	40	$3,020.00
1042	7/16/2023	10/15/2023	2024	40	$2,620.00
1043	4/30/2023	7/29/2023	2023	40	$3,020.00
1044	4/30/2023	7/14/2023	2023	40	$3,020.00
1045	8/21/2023	11/25/2023	2024	40	$2,620.00
1046	5/16/2023	7/7/2023	2023	40	$3,289.60
1047	8/7/2023	10/21/2023	2024	40	$2,620.00
1048	5/22/2023	8/19/2023	2023	40	$3,020.00
1049	9/6/2023	11/18/2023	2024	40	$2,620.00
1050	4/30/2023	8/5/2023	2023	32	$863.68
1051	4/16/2023	7/15/2023	2023	32	$863.68
1052	7/16/2023	10/14/2023	2024	32	$703.68
1053	7/3/2023	9/13/2023	2024	36	$2,420.64
1054	10/9/2023	1/13/2024	2024	36	$2,420.64
1055	5/1/2023	7/29/2023	2023	36	$2,960.64
1056	7/30/2023	10/9/2023	2024	36	$2,420.64
1057	6/26/2023	9/23/2023	2023	36	$2,960.64
1058	9/24/2023	12/24/2023	2024	36	$2,420.64
1059	4/23/2023	8/5/2023	2023	36	$2,960.64
1060	9/11/2023	12/23/2023	2024	36	$2,420.64
1061	5/1/2023	7/29/2023	2023	24	$1,973.76
1062	7/30/2023	10/21/2023	2024	24	$1,613.76
1063	5/8/2023	8/2/2023	2023	36	$2,960.64
1064	8/7/2023	11/4/2023	2024	36	$2,420.64
1065	5/28/2023	8/27/2023	2023	36	$2,420.64
1066	8/28/2023	12/16/2023	2024	36	$2,420.64
1067	5/8/2023	8/5/2023	2023	36	$2,611.80
1068	8/6/2023	11/4/2023	2024	36	$2,431.80
1069	11/5/2023	12/8/2023	2024	36	$2,431.80
1070	6/18/2023	7/5/2023	2023	40	$1,988.00
1071	8/7/2023	10/21/2023	2024	40	$1,988.00
1072	10/22/2023	1/6/2024	2024	40	$1,988.00
1073	5/1/2023	7/29/2023	2023	40	$2,588.00
1074	5/28/2023	9/2/2023	2023	40	$2,588.00
1075	9/11/2023	12/9/2023	2024	40	$1,988.00
1076	10/15/2023	1/13/2024	2024	32	$703.68
1077	6/4/2023	9/3/2023	2023	36	$2,178.00
1078	4/16/2023	7/31/2023	2023	36	$2,178.00
1079	8/1/2023	10/28/2023	2024	48	$2,184.00
1080	4/24/2023	7/29/2023	2023	36	$2,178.00
1081	7/30/2023	9/30/2023	2024	36	$1,638.00
1082	10/8/2023	1/27/2024	2024	36	$1,638.00
1083	5/8/2023	8/30/2023	2023	36	$2,178.00
1084	9/11/2023	12/14/2023	2024	36	$1,638.00
1085	6/4/2023	9/30/2023	2023	36	$2,178.00
1086	10/1/2023	12/30/2023	2024	36	$1,638.00
1087	9/10/2023	12/10/2023	2024	36	$2,178.00
1088	5/22/2023	8/19/2023	2023	36	$2,960.64
1089	5/28/2023	9/9/2023	2023	36	$2,960.64
1090	4/24/2023	7/22/2023	2023	36	$2,538.00
1091	8/7/2023	8/25/2023	2024	40	$939.20
1092	8/31/2023	12/2/2023	2024	36	$1,998.00
1093	4/23/2023	8/12/2023	2023	36	$1,406.88
1094	8/13/2023	11/25/2023	2024	36	$1,406.88
1095	5/21/2023	8/19/2023	2023	36	$1,998.00
1096	5/28/2023	8/26/2023	2023	36	$1,998.00
1097	4/24/2023	7/22/2023	2023	40	$2,029.20
1098	5/30/2023	8/26/2023	2023	36	$1,638.00
1099	9/6/2023	12/2/2023	2024	36	$1,638.00
1100	10/30/2023	1/27/2024	2024	36	$1,098.00
1101	5/1/2023	7/29/2023	2023	40	$939.20
1102	8/6/2023	9/8/2023	2024	40	$1,165.60
1103	5/7/2023	8/5/2023	2023	40	$2,004.40
1104	9/18/2023	12/9/2023	2024	40	$1,989.60
1105	5/29/2023	9/30/2023	2023	36	$2,178.00
1106	10/1/2023	2/17/2024	2024	36	$1,638.00
1107	5/30/2023	8/26/2023	2023	36	$1,998.00
1108	9/6/2023	12/2/2023	2024	36	$1,998.00
1109	6/26/2023	9/23/2023	2023	36	$2,178.00
1110	6/4/2023	9/2/2023	2023	40	$2,588.00
1111	6/4/2023	9/2/2023	2023	36	$2,898.00
1112	9/3/2023	11/6/2023	2024	36	$1,998.00
1113	12/7/2023	3/9/2024	2024	36	$1,998.00
1114	6/4/2023	7/20/2023	2023	36	$1,998.00
1115	6/5/2023	9/2/2023	2023	40	$879.60
1116	9/11/2023	12/30/2023	2024	40	$879.60
1117	6/5/2023	9/9/2023	2023	40	$939.20
1118	6/5/2023	9/2/2023	2023	36	$1,638.00
1119	9/3/2023	12/9/2023	2024	36	$1,638.00
1120	8/14/2023	11/4/2023	2024	40	$2,588.00
1121	9/11/2023	12/9/2023	2024	40	$1,788.00
1122	6/12/2023	9/9/2023	2023	36	$1,826.28
1123	6/26/2023	9/9/2023	2023	40	$1,389.60
1124	6/12/2023	9/9/2023	2023	36	$2,178.00
1125	6/18/2023	9/30/2023	2023	36	$1,638.00
1126	6/18/2023	9/9/2023	2023	40	$879.60
1127	9/10/2023	12/9/2023	2024	40	$879.60
1128	6/19/2023	9/16/2023	2023	40	$820.00
1129	9/17/2023	10/28/2023	2024	40	$820.00
1130	6/19/2023	7/24/2023	2023	40	$1,389.60
1131	8/21/2023	11/10/2023	2024	40	$1,389.60
1132	8/14/2023	11/11/2023	2024	40	$1,213.60
1133	7/2/2023	7/15/2023	2024	48	$3,384.00
1134	8/15/2023	11/11/2023	2024	48	$2,184.00
1135	6/20/2023	9/16/2023	2023	40	$848.00
1136	6/25/2023	10/14/2023	2023	36	$1,998.00
1137	10/15/2023	12/26/2023	2024	36	$1,998.00
1138	6/26/2023	9/23/2023	2023	40	$2,702.00
1139	10/16/2023	1/13/2024	2024	40	$2,702.00
1140	10/9/2023	1/6/2024	2024	36	$1,998.00
1141	8/20/2023	11/18/2023	2024	36	$2,178.00
1142	7/2/2023	9/30/2023	2024	36	$2,178.00
1143	10/7/2023	1/27/2024	2024	36	$1,638.00
1144	6/27/2023	9/23/2023	2023	36	$2,178.00
1145	8/7/2023	11/4/2023	2024	36	$791.64
1146	11/5/2023	2/10/2024	2024	36	$791.64
1147	7/2/2023	9/30/2023	2024	40	$2,029.20
1148	10/1/2023	12/22/2023	2024	40	$2,029.20
1149	10/23/2023	1/20/2024	2024	36	$1,098.00
1150	7/3/2023	9/30/2023	2024	36	$1,693.08
1151	7/3/2023	9/30/2023	2024	36	$2,178.00
1152	9/11/2023	12/16/2023	2024	48	$1,667.52
1153	7/3/2023	9/30/2023	2024	36	$2,178.00
1154	7/10/2023	10/7/2023	2024	36	$738.00
1155	7/10/2023	9/30/2023	2024	48	$2,904.00
1156	7/10/2023	7/27/2023	2024	36	$1,638.00
1157	7/31/2023	10/28/2023	2024	36	$1,638.00
1158	10/30/2023	1/27/2024	2024	36	$1,998.00
1159	7/17/2023	10/14/2023	2024	40	$1,389.60
1160	10/15/2023	4/13/2024	2024	40	$1,389.60
1161	7/17/2023	10/14/2023	2024	36	$1,998.00
1162	7/17/2023	10/21/2023	2024	40	$820.00
1163	7/17/2023	9/5/2023	2024	40	$879.60
1164	9/25/2023	12/23/2023	2024	40	$1,389.60
1165	7/24/2023	10/21/2023	2024	40	$1,850.00
1166	7/24/2023	10/21/2023	2024	36	$1,998.00
1167	10/22/2023	1/27/2024	2024	36	$1,998.00
1168	9/11/2023	12/23/2023	2024	36	$1,278.00
1169	8/7/2023	11/4/2023	2024	40	$2,620.00
1170	7/31/2023	10/28/2023	2024	40	$2,829.20
1171	10/29/2023	2/11/2024	2024	40	$2,702.00

Re: Total seems correct but row values in table visual seem incorrect

Greg_Deckler — Fri, 13 Oct 2023 17:19:07 GMT

cathoms Seems like I am missing Start FY column in your sample data.

Re: Total seems correct but row values in table visual seem incorrect

cathoms — Fri, 13 Oct 2023 18:00:24 GMT

Oops! Edited that post and updated data with missing column and additional rows. Thanks!

Re: Total seems correct but row values in table visual seem incorrect

Greg_Deckler — Fri, 13 Oct 2023 22:25:14 GMT

cathoms OK, I have everything working, or at least returning values. My question now as I look at this further is what are you trying to calculate? The way you have your comparison operators setup it looks like you are calculating for all values where the Start Date is previous to the current end of month as well as all values where the End Date is greater than the current end of the month. So in July 2023, you would get all rows where the Start Date is less than 7/31/2023 AND the End Date is greater than 7/31/2023. Is that what is intended??

Re: Total seems correct but row values in table visual seem incorrect

cathoms — Mon, 16 Oct 2023 13:20:48 GMT

Hmmm... my goal with the Weeks in FY 24 measure was to calculate the number of weeks each contract is active in FY 24 - so if someone started before or on 1 July and ended 31 August, the measure should return eight weeks (roughly). In a visual the filter context should show four weeks in July and four weeks in August. If a contract started the second week of July and ended after the second week of August, it should return roughly five weeks - three in July and two in August, etc.

Ultimately I want the ability to show a month by month value for contracts in the fiscal year that sums correctly. So, for example, what is the total incremental cost of active contracts for the month of July? What is the incremental cost of active contracts in August, and so forth. Those should add up to the total for the year. My client wants to see the progression of our incremental costs vs. our budget for travelers and has asked for a waterfall or funnel chart.

Does that help?

Re: Total seems correct but row values in table visual seem incorrect

Greg_Deckler — Mon, 16 Oct 2023 18:13:20 GMT

cathoms
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

Especially this one, Pediodic Billing:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Re: Total seems correct but row values in table visual seem incorrect

cathoms — Mon, 16 Oct 2023 20:25:01 GMT

Thanks for that. I'll admit I don't quite understand what is going on in those measures nor how to transfer that approach. I think I would use weekly increment in place of Amount, but that is a weekly rate rather than monthly, so how do I account for that?

If you have time, could you show what that Periodic Billing quick measure should look like with my data?

Re: Total seems correct but row values in table visual seem incorrect

Greg_Deckler — Tue, 17 Oct 2023 13:30:48 GMT

cathoms See what you think about this. PBIX attached below signature:

Total Amount = VAR tmpCalendar = ADDCOLUMNS('DateDim',"MonthYear", YEAR([DateValue])*100+MONTH([DateValue])) VAR tmpBilling = ADDCOLUMNS('TravelerFactCurrentFY',"MonthYearBegin",YEAR([Start Date])*100+MONTH(([Start Date])), "MonthYearEnd",YEAR([End Date])*100+MONTH([End Date])) VAR tmpTable = FILTER( GENERATE( tmpBilling, SUMMARIZE(tmpCalendar,[MonthYear]) ), [MonthYear] >= [MonthYearBegin] && [MonthYear] <= [MonthYearEnd] ) RETURN SUMX(tmpTable,[WeeklyIncrmt])

Re: Total seems correct but row values in table visual seem incorrect

cathoms — Tue, 17 Oct 2023 15:38:38 GMT

I'll need to check which fields are leading to this error:

Re: Total seems correct but row values in table visual seem incorrect

cathoms — Tue, 17 Oct 2023 16:33:57 GMT

Greg_Deckler, thanks for all of your effort. I will troubleshoot that error message because that seems like a potentially useful approach in other cases. However, I'm under some time pressure so I'm taking a more brute force approach, at least for the time being.

I created a separate table and therein generate a row for each week between contract start and end, yielding the weekly increment by week for each contract. So, if a contract was active for six weeks for example, I now have six rows for that contract and a column with the weekly increment for each of those weeks (say $972). Summing the weekly increment returns the correct total by selected time period (e.g. $5,832 for all six weeks, about $3,888 for a month, etc.). Actually it's a bit of an estimate because there may be partial weeks if someone ends a contract early or starts in the middle of a week, but close enough for my purposes.

The following video shows how to create separate rows for each month start date. I adapted it to weeks because I have weekly pay rate data: Generate Dates between Start and End Date in Power Query - YouTube.