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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi v-jiascu-msft,

     

    File has been sent to you

     

    Regards

    BDA2

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

    Hi Anonymous,

     

    Did it work? Can you mark the proper answer as a solution?

     

    Best Regards,

    Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi v-jiascu-msft

     

    Yes it works. However, for grouping that's not what I want.

    For grouping I am expecting the visual in a table form meaning table with darken borders for the same group of sales order, not grouping as a summary by measure.