Column to calculate measure based on last Working day

[nbarjesteh]

Hi- I am trying to create a column that will return a measure (Run time) based on the last working day.  I have successfully created a WorkDayID for all my working days, but I am not sure how to calculate the Run time for the previous working day based on the WorkDayID.  Appreciate any help!

[v-janeyg-msft · Accepted solution]

Hi, @nbarjesteh 

If your data is as simple as this, you just need to create a 1-many relationship in date columns, then you can put workdayid and runtime(form of sum) in table visual.

Best Regards,
Community Support Team _ Janey

[v-janeyg-msft · Accepted solution]

Hi, @nbarjesteh 

I check your sample and create a measure according to your requirement.

Like this:

Measure =
VAR a =
    MAXX (
        FILTER (
            ALL ( 'Command Tooling Systems, LLC$Capacity Ledger Entry' ),
            RELATED ( 'Date Table'[WorkDayID] ) <> BLANK ()
                && RELATED ( 'Date Table'[Date] ) < TODAY ()
        ),
        RELATED ( 'Date Table'[Date] )
    )
RETURN
    SUMX (
        FILTER (
            ALL ( 'Command Tooling Systems, LLC$Capacity Ledger Entry' ),
            [Posting Date] = a
        ),
        [Run Time]
    )

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[nbarjesteh · Accepted solution]

HI @v-janeyg-msft -PERFECT-WORKS today. I think it will work on monday after the weekend. VERY much appreciate your help.  I am still very early in my DAX proficiency, and it really helps to have this community to help when I get stuck.

[v-janeyg-msft · Accepted solution]

Hi, @nbarjesteh 

Add this in formula:

&&RELATED('Machine Type'[Cell])="Soft"

If you still have problems, please post a new post. We will actively help you, but your knowledge is weak, you can learn it yourself first.

DAX overview - DAX | Microsoft Docs

DAX Basics in Power BI (tutorialspoint.com)

Why you should use DAX Studio with Power BI - YouTube

Best Regards,
Community Support Team _ Janey

[nbarjesteh]

Hi @v-janeyg-msft ,

If I may impose 1 last time for this issue.  Along with the 1 measure you helped me create, I have a table that details all the Scrap transactions for the previous workday.  Currently, I have a date filter that I have to change every Monday to look back 3 days (prior to the Weekend).  I can I create the table to update automatically to the last working day and pull the correct details of each scrap transaction?  Please see link for details.   Scrap Table (Date should be last working day and should pull in related data based on that day) 

[v-janeyg-msft]

@nbarjesteh  Please create a new post and describe your problem. Thank you.

Best Regards,
Community Support Team _ Janey

[v-janeyg-msft]

Hi, @nbarjesteh 

I created a sample to meet your needs.

Like this:

Measure = MAXX(FILTER(ALL('Table'),[WorkdayID]=SELECTEDVALUE('Table'[WorkdayID])-1),[Run Time Sum])

If your WorkdayID  and Run Time Sum are measures.

Try this:

Measure2 =
VAR idd = [WorkdayID Measure]
RETURN
    MAXX (
        FILTER (
            SUMMARIZE (
                ALL ( 'Table' ),
                'Table'[Date],
                "ID", [WorkdayID Measure],
                "SUM", [Run Time Sum Measure]
            ),
            [ID] = idd - 1
        ),
        [SUM]
    )

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[nbarjesteh]

Hi, 

Here is a little more clarification. I want to return the correct run time sum (Green highlight) Thanks for your support!

[v-janeyg-msft · Accepted solution]

Hi, @nbarjesteh 

If your data is as simple as this, you just need to create a 1-many relationship in date columns, then you can put workdayid and runtime(form of sum) in table visual.

Best Regards,
Community Support Team _ Janey

[nbarjesteh]

Hi @v-janeyg-msft ,  Thanks again for helping.  I get this message when trying to create the relationship 1-Many:

[v-janeyg-msft]

Hi, @nbarjesteh 

If the data is like what you have provided, there is no possibility of problems.

Like this:

But your error is obviously because you have a many-to-many relationship between your dates. 

I'm not sure if a many-to-many relationship will affect the results. Please share a representative sample.

Best Regards,
Community Support Team _ Janey

[nbarjesteh]

Hi!  Here is a link to my file.  Again, thank  you for you help.  As described earlier, I am trying to create a measure that will update the "Output Last Working Day" Card with the run time sum for the last working day.  Currently, I have to update the visual manually by adjusting the relative date filter.  We don't run on the weekends or holdidays, so on Mondays or after a holiday.  I need to sum the runtime from the last working day (not yesterday).  

pbix file link 

[v-janeyg-msft · Accepted solution]

Hi, @nbarjesteh 

I check your sample and create a measure according to your requirement.

Like this:

Measure =
VAR a =
    MAXX (
        FILTER (
            ALL ( 'Command Tooling Systems, LLC$Capacity Ledger Entry' ),
            RELATED ( 'Date Table'[WorkDayID] ) <> BLANK ()
                && RELATED ( 'Date Table'[Date] ) < TODAY ()
        ),
        RELATED ( 'Date Table'[Date] )
    )
RETURN
    SUMX (
        FILTER (
            ALL ( 'Command Tooling Systems, LLC$Capacity Ledger Entry' ),
            [Posting Date] = a
        ),
        [Run Time]
    )

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[nbarjesteh]

Hi-One more quick question.  How would I add another filter to the measure to only add up Run time from "Soft" cell.  See file attached.  Thanks!!

Link to pbix file 

[nbarjesteh · Accepted solution]

HI @v-janeyg-msft -PERFECT-WORKS today. I think it will work on monday after the weekend. VERY much appreciate your help.  I am still very early in my DAX proficiency, and it really helps to have this community to help when I get stuck.

[Ashish_Mathur]

Hi,

In an MS Excel file, please show the desired result via formulas in a column.  Please also share the link from where i can download your PBI file.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome.