How to calculate the sum required here?

[Anonymous]

I ahve Table :

For each EN I want to calculate the difference of Updated_date for new_etape_en_id = 1 and 11 and obtain the overall sum of the difference.

Eg- for ([Updated_date] difference where [new_etape_en_id] = 1 and 11) for EN=111 + ([Updated_date] difference where [new_etape_en_id] = 1 and 11) for EN=20211019-02 + () +().....and so on.

How do I achieve the same?

Any help would be appreciated.

Thanks in advance

[v-cazheng-msft]

Hi @Anonymous 

You can try this Measure.

Diff_EN_MIN =

VAR diff =

    ADDCOLUMNS (

        'Table',

        "min_",

            CALCULATE (

                MIN ( 'Table'[Update_date] ),

                FILTER ( ALL ( 'Table' ), 'Table'[EN] = MIN ( 'Table'[EN] ) )

            )

    )

RETURN

    SUMX ( diff, DATEDIFF ( [min_], 'Table'[Update_date], MINUTE ) )

The result looks like this:

Also, attached the pbix file as a reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous]

Hey @v-cazheng-msft , Thanks for solution

If for the same EN , I have two records of new_etape_id =11 ,then I want to consider 11 which has greater updated date.
What changes can be made in the measure?!

[v-cazheng-msft · Accepted solution]

Hi @Anonymous 

Please make some changes to Diff_EN_MIN measure as follows:

Diff_EN_MIN =

VAR max_ =

    CALCULATE ( MAX ( 'Table'[Update_date] ), ALLEXCEPT ( 'Table', 'Table'[EN] ) )

VAR min_ =

    CALCULATE (

        MIN ( 'Table'[Update_date] ),

        /*FILTER ( ALL ( 'Table' ), 'Table'[EN] = MAX(  'Table'[EN] ) )*/

        ALLEXCEPT (

            'Table',

            'Table'[EN]

        )

    )

VAR minus =

    IF (

        SELECTEDVALUE ( 'Table'[Update_date] ) = max_,

        DATEDIFF ( min_, max_, MINUTE ),

        DATEDIFF ( MIN ( 'Table'[Update_date] ), MAX ( 'Table'[Update_date] ), MINUTE )

    )

RETURN

    minus

And then create another measure with it.

Sum_Diff_EN_MIN = SUMX('Table',[Diff_EN_MIN])

Then use the new measure to create your visual, result will look like this:

Also, attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous]

Thanks you for the help @v-cazheng-msft !

How do I create a summarized table containing only 1 and 11 in a measure if I have records in new_etape_id other than 1 and 11. I mean my table also contains new_etape_id values other than 1 and 11 but I want my ans only for 1 and 11 as done by you.

I wrote following query for it but didnt get correct ans

I just amended your Sum_diff_EN_Min measure. Rest all is same.

Please help.

[VahidDM]

Hi @Anonymous 

Your request is not clear to me, Do you want the difference in Day or Hour format?  Can you add a result table to your request?

BTW, try this:

Measure = 
VAR _A =
    SUMMARIZE(
        table,
        table[EN],
        "Diff",
            DATEDIFF(
                CALCULATE( MAX( [updated_date], [new_etape_en_id] = 1 ) ),
                CALCULATE( MAX( [updated_date], [new_etape_en_id] = 11 ) ),
                DAY
            )
    )
RETURN
    SUMX( _A, [Diff] )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Greg_Deckler]

@Anonymous Not sure I am following what you are trying to do. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

If not, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hey @Greg_Deckler !

I dont just want current-previous. I want date difference for new_etape_en_id = 1 and 11 for each EN and a sum of those difference which I can plot in card visual.