topic Re: Grouping and Dates Formula in Desktop

Grouping and Dates Formula

Anonymous — Tue, 12 Jun 2018 02:12:38 GMT

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 screenshot. You may provide steps or screenshot.

From the table above, I am using search filter to drill down my SO numbers. Can someone help me to formulate the DAX code/custom collumn to measure Latest.(Date of Change) for (New Del Value Date )- Earliest.(Date of Change) for (Old Value Date)

So the result should be number of days= 15 Sept 2018 - 15 June 2018 = 92 Days

Re: Grouping and Dates Formula

v-jiascu-msft — Thu, 14 Jun 2018 03:14:00 GMT

Hi Anonymous,

Try this formula please.

Result =
CALCULATE (
    DATEDIFF (
        MIN ( 'table'[Old Value Date] ),
        MAX ( 'table'[New Del Value Date] ),
        DAY
    ),
    ALLEXCEPT ( 'table', 'table'[Sales Order] )
)

Best Regards,

Dale

Re: Grouping and Dates Formula

Anonymous — Thu, 14 Jun 2018 05:20:08 GMT

@ v-jiascu-msft

Thank you for replying.

However, the formula returns me the value of 122 days, which is incorrect.

92 days should be the answer.

Besides, can you help me in grouping the SO numbers in table ?

So I can see how many repeated times per SO number. Shown below screenshot. You may provide steps or screenshot.

Re: Grouping and Dates Formula

v-jiascu-msft — Thu, 14 Jun 2018 06:04:15 GMT

Hi Anonymous,

Can you share the data in TEXT mode or share the file? It's a hard work to type so many words.

1. Can you post a snapshot? It should work in the table visual of the second snapshot of your first post.

2. What the format of table do you expect? Maybe you can try the following formula.

Table =
SUMMARIZE (
    'salestable',
    'salestable'[Sales Order],
    "Times", COUNT ( 'salestable'[Sales Order] )
)

Best Regards,

Dale

Re: Grouping and Dates Formula

Anonymous — Thu, 14 Jun 2018 08:06:08 GMT

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.

Re: Grouping and Dates Formula

Anonymous — Tue, 19 Jun 2018 00:40:08 GMT

v-jiascu-msft

Let me know if you are getting my replies.

Looking forward to hear a solution from you soon.

Re: Grouping and Dates Formula

v-jiascu-msft — Tue, 19 Jun 2018 01:52:07 GMT

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

Best Regards,

Dale

Re: Grouping and Dates Formula

Anonymous — Wed, 20 Jun 2018 07:28:37 GMT

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.

Re: Grouping and Dates Formula

v-jiascu-msft — Wed, 20 Jun 2018 07:42:31 GMT

Can you share your file?

Best Regards,

Dale

Re: Grouping and Dates Formula

Anonymous — Mon, 25 Jun 2018 03:53:33 GMT

Hi v-jiascu-msft,

File has been sent to you

Regards

BDA2

Re: Grouping and Dates Formula

v-jiascu-msft — Wed, 27 Jun 2018 09:42:18 GMT

Hi Anonymous,

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

Best Regards,

Dale

Re: Grouping and Dates Formula

Anonymous — Fri, 29 Jun 2018 01:04:57 GMT

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.