Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Grouping and Dates Formula

I have a set of table with repeated SO numbers. Can someone assit me in grouping the SO numbers together in the table with borders, so I can see how many repeated times per SO number. Shown below ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    I modify the old formula. Please try it again.

    Result =
VAR minChangeDate =
    CALCULATE ( MIN ( 'Table'[Date of Change] ), ALL ( 'Table' ) )
VAR maxChangeDate =
    CALCULATE ( MAX ( 'Table'[Date of Change] ), ALL ( 'Table' ) )
RETURN
    DATEDIFF (
        CALCULATE (
            MIN ( 'table'[Old Value Date] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date of Change] = minChangeDate )
        ),
        CALCULATE (
            MAX ( 'table'[New Del Value Date] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date of Change] = maxChangeDate )
        ),
        DAY
    )

    About grouping:

    grouping =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Sales Order],
        [Old Value Date],
        [New Del Value Date],
        [Date of Change],
        "Value", [Result]
    ),
    [Value]
)

    Grouping_and_Dates_Formula

     

    Best Regards,

    Dale

Anonymous's avatar
Anonymous
Not applicable
8 years ago

v-jiascu-msft

 

The file is propietary.

It will take awhile for me to recreate the file.

 

1)Below is the screenshot please refer.

The actual result should be 92 days instead of 122days

2) As for grouping Sales Order, I am expecting something as of below, darken frame shown below. More with visualization.

If possible to show sum below each sales order group.

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

v-jiascu-msft

 

Let me know if you are getting my replies.

Looking forward to hear a solution from you soon.

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    I modify the old formula. Please try it again.

    Result =
VAR minChangeDate =
    CALCULATE ( MIN ( 'Table'[Date of Change] ), ALL ( 'Table' ) )
VAR maxChangeDate =
    CALCULATE ( MAX ( 'Table'[Date of Change] ), ALL ( 'Table' ) )
RETURN
    DATEDIFF (
        CALCULATE (
            MIN ( 'table'[Old Value Date] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date of Change] = minChangeDate )
        ),
        CALCULATE (
            MAX ( 'table'[New Del Value Date] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date of Change] = maxChangeDate )
        ),
        DAY
    )

    About grouping:

    grouping =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[Sales Order],
        [Old Value Date],
        [New Del Value Date],
        [Date of Change],
        "Value", [Result]
    ),
    [Value]
)

    Grouping_and_Dates_Formula

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-jiascu-msft

       

      Thank you for helping out. However, I still don't get the result. Not too sure why when all script are correct, but when i input the formula I don't get 92 days instead error of 243 days.

       

      As for the grouping, that is not the outcome I needed.

       

      I have move on with an alternative visual and analyzing method.

       

      Thank you once again.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Can you share your file?

         

        Best Regards,

        Dale