Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
cathoms
Helper V
Helper V

Total seems correct but row values in table visual seem incorrect

‎10-12-2023 01:44 PM

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. 

 

cathoms_1-1697143022665.png

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!

 

Labels:
Message 1 of 12
621 Views
0
1 ACCEPTED SOLUTION
cathoms
Helper V
Helper V

‎10-17-2023 09:33 AM

@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.

 

View solution in original post

Message 12 of 12
454 Views
0
11 REPLIES 11
cathoms
Helper V
Helper V

‎10-17-2023 09:33 AM

@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.

 

Message 12 of 12
455 Views
0
cathoms
Helper V
Helper V

‎10-13-2023 09:47 AM

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.

 

IndexStart DateEnd DateStart FYWeekly HourWeeklyIncrmt
10011/19/20237/22/2023202336$738.00
10027/23/202310/21/2023202436$738.00
10035/22/20238/19/2023202340$820.00
100410/22/20231/18/2024202436$738.00
10057/16/202310/15/2023202440$879.60
10066/4/20239/2/2023202340$879.60
10075/28/20238/26/2023202340$879.60
10084/9/20237/29/2023202340$1,079.60
10098/20/202311/19/2023202440$879.60
10104/10/20237/8/2023202340$1,079.60
10117/23/20239/2/2023202440$879.60
10124/17/20237/15/2023202340$1,079.60
101310/16/202312/23/2023202440$879.60
10145/28/20238/26/2023202348$1,667.52
10155/28/20237/11/2023202336$1,250.64
10168/14/202311/11/2023202440$1,213.60
10174/17/20237/22/2023202348$1,295.52
101810/2/202312/30/2023202432$703.68
10194/18/20238/5/2023202340$1,079.60
10208/6/202311/4/2023202440$879.60
102111/5/202312/24/2023202440$879.60
10224/23/20237/14/2023202336$738.00
102311/6/20232/3/2024202440$1,389.60
10244/24/20237/22/2023202340$1,389.60
10259/5/202312/2/2023202440$879.60
10268/28/202311/26/2023202448$1,667.52
10274/30/20237/30/2023202340$1,079.60
10289/1/202310/14/2023202440$879.60
10295/1/20237/29/2023202340$1,079.60
10305/8/20238/5/2023202340$1,389.60
10318/6/20239/16/2023202440$1,389.60
10329/25/202312/23/2023202440$879.60
10339/3/202312/3/2023202440$879.60
10348/20/202311/18/2023202436$738.00
103510/30/20231/27/2024202440$879.60
103610/8/20231/6/2024202440$879.60
103711/6/20232/3/2024202440$820.00
10389/5/20239/29/2023202440$879.60
10392/6/20238/12/2023202340$3,020.00
10408/13/202310/20/2023202440$2,620.00
10414/19/20237/15/2023202340$3,020.00
10427/16/202310/15/2023202440$2,620.00
10434/30/20237/29/2023202340$3,020.00
10444/30/20237/14/2023202340$3,020.00
10458/21/202311/25/2023202440$2,620.00
10465/16/20237/7/2023202340$3,289.60
10478/7/202310/21/2023202440$2,620.00
10485/22/20238/19/2023202340$3,020.00
10499/6/202311/18/2023202440$2,620.00
10504/30/20238/5/2023202332$863.68
10514/16/20237/15/2023202332$863.68
10527/16/202310/14/2023202432$703.68
10537/3/20239/13/2023202436$2,420.64
105410/9/20231/13/2024202436$2,420.64
10555/1/20237/29/2023202336$2,960.64
10567/30/202310/9/2023202436$2,420.64
10576/26/20239/23/2023202336$2,960.64
10589/24/202312/24/2023202436$2,420.64
10594/23/20238/5/2023202336$2,960.64
10609/11/202312/23/2023202436$2,420.64
10615/1/20237/29/2023202324$1,973.76
10627/30/202310/21/2023202424$1,613.76
10635/8/20238/2/2023202336$2,960.64
10648/7/202311/4/2023202436$2,420.64
10655/28/20238/27/2023202336$2,420.64
10668/28/202312/16/2023202436$2,420.64
10675/8/20238/5/2023202336$2,611.80
10688/6/202311/4/2023202436$2,431.80
106911/5/202312/8/2023202436$2,431.80
10706/18/20237/5/2023202340$1,988.00
10718/7/202310/21/2023202440$1,988.00
107210/22/20231/6/2024202440$1,988.00
10735/1/20237/29/2023202340$2,588.00
10745/28/20239/2/2023202340$2,588.00
10759/11/202312/9/2023202440$1,988.00
107610/15/20231/13/2024202432$703.68
10776/4/20239/3/2023202336$2,178.00
10784/16/20237/31/2023202336$2,178.00
10798/1/202310/28/2023202448$2,184.00
10804/24/20237/29/2023202336$2,178.00
10817/30/20239/30/2023202436$1,638.00
108210/8/20231/27/2024202436$1,638.00
10835/8/20238/30/2023202336$2,178.00
10849/11/202312/14/2023202436$1,638.00
10856/4/20239/30/2023202336$2,178.00
108610/1/202312/30/2023202436$1,638.00
10879/10/202312/10/2023202436$2,178.00
10885/22/20238/19/2023202336$2,960.64
10895/28/20239/9/2023202336$2,960.64
10904/24/20237/22/2023202336$2,538.00
10918/7/20238/25/2023202440$939.20
10928/31/202312/2/2023202436$1,998.00
10934/23/20238/12/2023202336$1,406.88
10948/13/202311/25/2023202436$1,406.88
10955/21/20238/19/2023202336$1,998.00
10965/28/20238/26/2023202336$1,998.00
10974/24/20237/22/2023202340$2,029.20
10985/30/20238/26/2023202336$1,638.00
10999/6/202312/2/2023202436$1,638.00
110010/30/20231/27/2024202436$1,098.00
11015/1/20237/29/2023202340$939.20
11028/6/20239/8/2023202440$1,165.60
11035/7/20238/5/2023202340$2,004.40
11049/18/202312/9/2023202440$1,989.60
11055/29/20239/30/2023202336$2,178.00
110610/1/20232/17/2024202436$1,638.00
11075/30/20238/26/2023202336$1,998.00
11089/6/202312/2/2023202436$1,998.00
11096/26/20239/23/2023202336$2,178.00
11106/4/20239/2/2023202340$2,588.00
11116/4/20239/2/2023202336$2,898.00
11129/3/202311/6/2023202436$1,998.00
111312/7/20233/9/2024202436$1,998.00
11146/4/20237/20/2023202336$1,998.00
11156/5/20239/2/2023202340$879.60
11169/11/202312/30/2023202440$879.60
11176/5/20239/9/2023202340$939.20
11186/5/20239/2/2023202336$1,638.00
11199/3/202312/9/2023202436$1,638.00
11208/14/202311/4/2023202440$2,588.00
11219/11/202312/9/2023202440$1,788.00
11226/12/20239/9/2023202336$1,826.28
11236/26/20239/9/2023202340$1,389.60
11246/12/20239/9/2023202336$2,178.00
11256/18/20239/30/2023202336$1,638.00
11266/18/20239/9/2023202340$879.60
11279/10/202312/9/2023202440$879.60
11286/19/20239/16/2023202340$820.00
11299/17/202310/28/2023202440$820.00
11306/19/20237/24/2023202340$1,389.60
11318/21/202311/10/2023202440$1,389.60
11328/14/202311/11/2023202440$1,213.60
11337/2/20237/15/2023202448$3,384.00
11348/15/202311/11/2023202448$2,184.00
11356/20/20239/16/2023202340$848.00
11366/25/202310/14/2023202336$1,998.00
113710/15/202312/26/2023202436$1,998.00
11386/26/20239/23/2023202340$2,702.00
113910/16/20231/13/2024202440$2,702.00
114010/9/20231/6/2024202436$1,998.00
11418/20/202311/18/2023202436$2,178.00
11427/2/20239/30/2023202436$2,178.00
114310/7/20231/27/2024202436$1,638.00
11446/27/20239/23/2023202336$2,178.00
11458/7/202311/4/2023202436$791.64
114611/5/20232/10/2024202436$791.64
11477/2/20239/30/2023202440$2,029.20
114810/1/202312/22/2023202440$2,029.20
114910/23/20231/20/2024202436$1,098.00
11507/3/20239/30/2023202436$1,693.08
11517/3/20239/30/2023202436$2,178.00
11529/11/202312/16/2023202448$1,667.52
11537/3/20239/30/2023202436$2,178.00
11547/10/202310/7/2023202436$738.00
11557/10/20239/30/2023202448$2,904.00
11567/10/20237/27/2023202436$1,638.00
11577/31/202310/28/2023202436$1,638.00
115810/30/20231/27/2024202436$1,998.00
11597/17/202310/14/2023202440$1,389.60
116010/15/20234/13/2024202440$1,389.60
11617/17/202310/14/2023202436$1,998.00
11627/17/202310/21/2023202440$820.00
11637/17/20239/5/2023202440$879.60
11649/25/202312/23/2023202440$1,389.60
11657/24/202310/21/2023202440$1,850.00
11667/24/202310/21/2023202436$1,998.00
116710/22/20231/27/2024202436$1,998.00
11689/11/202312/23/2023202436$1,278.00
11698/7/202311/4/2023202440$2,620.00
11707/31/202310/28/2023202440$2,829.20
117110/29/20232/11/2024202440$2,702.00
Message 3 of 12
566 Views
0
Greg_Deckler
Super User
Super User
In response to cathoms

‎10-13-2023 10:19 AM

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Message 4 of 12
559 Views
0
cathoms
Helper V
Helper V
In response to Greg_Deckler

‎10-13-2023 11:00 AM

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

Message 5 of 12
553 Views
0
Greg_Deckler
Super User
Super User
In response to cathoms

‎10-13-2023 03:25 PM

@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??


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Message 6 of 12
538 Views
0
cathoms
Helper V
Helper V
In response to Greg_Deckler

‎10-16-2023 06:20 AM

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?

Message 7 of 12
497 Views
0
Greg_Deckler
Super User
Super User
In response to cathoms

‎10-16-2023 11:13 AM

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Message 8 of 12
490 Views
cathoms
Helper V
Helper V
In response to Greg_Deckler

‎10-16-2023 01:25 PM

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?

Message 9 of 12
485 Views
0
Greg_Deckler
Super User
Super User
In response to cathoms

‎10-17-2023 06:30 AM

@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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
WeekIncCost.pbix
Message 10 of 12
472 Views
cathoms
Helper V
Helper V
In response to Greg_Deckler

‎10-17-2023 08:38 AM

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

cathoms_0-1697557104592.png

 

Message 11 of 12
463 Views
0
Greg_Deckler
Super User
Super User

‎10-12-2023 02:38 PM

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Message 2 of 12
594 Views

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All