Labor Hours summed by date of square footage

DAX DAX Calculations
1

I have 2 fact tables

  1. Labor hours by date
  2. Square Footage by date

If there are labor hrs but no sqft for the same date the labor hrs need to be added to the 1st date after that has sqft. Here is an example of the 2 fact tables and the desired results.

date labor hrs date Sqft date Sqft labor hrs
10/28/2020 17.00 10/28/2020 10/28/2020
10/29/2020 33.50 10/29/2020 10/29/2020
10/30/2020 40.25 10/30/2020 4,364.00 10/30/2020 4,364.00 90.75
10/31/2020 14.25 10/31/2020 10/31/2020
11/1/2020 52.50 11/1/2020 11/1/2020
11/2/2020 46.00 11/2/2020 3,415.00 11/2/2020 3,415.00 112.75
2

Just talking out loud, This one sounds like something I would explore the “dates between” where your start date and end date would need to be a parameter that finds when these situations start and stop

3

Hi BernM,

Happy New Year!
If the example below seems to meet your expectations, why don’t you try the attached solution?

image

Labor Hours =
VAR NextDate = CALCULATE(
    MAX( 'Date'[Date] ),
    FILTER(
        ALLSELECTED( 'Date'[Date] ),
      'Date'[Date] < SELECTEDVALUE( 'Date'[Date] )
    ),
    FactTable_2 [Sqft] > 0
) 
VAR MaxDateWithSqft = MAXX(
    FILTER(
        SUMMARIZE( 'Date','Date'[Date], FactTable_2 [Sqft] ),
        FactTable_2 [Sqft] > 0
    ),
    [Date]
) 
VAR PrevDate = CALCULATE(
    FIRSTNONBLANK(
      'Date'[Date],
        [SumOfSqft] + [SumOfLabourHours]
    ),
    FILTER(
        ALL( 'Date' ),
      'Date'[Date] <= SELECTEDVALUE( 'Date'[Date] )
    )
) 
VAR Result1 = CALCULATE(
    [SumOfLabourHours],
    FILTER(
        ALLSELECTED( 'Date'[Date] ),
      'Date'[Date] > NextDate &&
     'Date'[Date] <= SELECTEDVALUE( 'Date'[Date] )
    )
) 
VAR Result2 = CALCULATE(
    [SumOfLabourHours],
  'Date'[Date] <= MaxDateWithSqft
) 

RETURN
IF(
    ISINSCOPE( 'Date'[Date] ),
    IF( [SumOfSqft] > 0, Result1 ),
    Result2
)

SumOfLabourHours =
SUM( FactTable_1 [Labor Hrs] )

SumOfSqft =
SUM( FactTable_2 [Sqft] )

File attached: LabourHours_001_20210103.pbix (69.0 KB)

Take care.
Mariusz

4

Hi @BernM, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

5

Hi @BernM, did the response provided by the users and experts helped you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

6

I’m so close using your calculations but I’m getting an error on the LH2 calc. It says "The column ‘Sqft’ specified in the ‘SUMMARIZE’ function was not found in the input table. I’m also getting the same error in the Labor Hours calculation. I have no idea why it isn’t working. :frowning_face:

7

Hi @BernM,

Have you resolved this?

The provided sample PBIX seems to be working without issue. Check your data model because both these measures call a column from your model that seems to be missing OR has a different name: FactTable_2[Sqft]

image

1 Like
8

Thank you Melissa for helping out as I was away :slight_smile:

1 Like
9

Hi @BernM, we’ve noticed that no response has been received from you since the 6th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

10

I got it to work!!! thank you so much for your help! I really appreciate it

1 Like