cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to calculate the sum required here?

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.

1 ACCEPTED SOLUTION
Community Support

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!

7 REPLIES 7
Community Support

Hi @Anonymous

You can try this Measure.

Diff_EN_MIN =

VAR diff =

'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
Not applicable

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?!

Community Support

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
Not applicable

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.

Super User

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.

Super User

@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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.