Need help regarding fine measure formulation

[mmvohra]

I want to make measure as per the following logic:
Filter table → only rows where [Dif KM_1] <= ParameterValue.

Within each Consolidated ID:

Find minimum date.
Find the immediate date after the minimum date which consecutive date

Get price at min date.

Get price at consecutive date.

Compute difference (min date – consecutive date.).

Sum the difference for all consolidated ID.

Basically it should find the consecutive difference in price within same Consolidated ID and then sums those difference for all ConsolidatedID.

URL of file
File URL sample pbix 

[v-karpurapud]

Hi @mmvohra 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @mmvohra 

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @Irwan , @Ashish_Mathur  and @danextian  for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution?If you have any more questions, please let us know and we’ll be happy to help.

Regards,

Microsoft Fabric Community Support Team.

[danextian]

Hi @mmvohra 

Please post your expected  result as well given the same sample data. If this were in Excel, how would you compute this?

[mmvohra]

Here is the revised link
https://drive.google.com/drive/folders/1gKyH3VLKGp7rJ58o4f10TIHXP6wh2mWz?usp=sharing

[Ashish_Mathur]

Hi,

Cannot understand your question.  Please show the expected result very clearly.  Also, there is no Dif KM_1 column in the table.

[mmvohra]

https://drive.google.com/drive/folders/1gKyH3VLKGp7rJ58o4f10TIHXP6wh2mWz?usp=sharing

[mmvohra]

Here is the revised link of excel and pbix file
https://drive.google.com/drive/folders/1gKyH3VLKGp7rJ58o4f10TIHXP6wh2mWz?usp=sharing

[Ashish_Mathur]

Hi,

The answer should be 20000.  I think the formula in cell I7 is wrong - it should be =[@Price]-$E$6.  PBI file attached.

[mmvohra]

I want table to be dynamically filtered based on parameter value and once the table is dynamically filtered only then it can sum up the difference between consecutive for each consolidatedID

[Ashish_Mathur · Accepted solution]

Hi,

Check now

[Irwan]

hello @mmvohra 

the file in the link seems to be missing.

Thank you.

[mmvohra]

File URL is updated, it contains power bi file and excel file telling what I want.
https://drive.google.com/drive/folders/1gKyH3VLKGp7rJ58o4f10TIHXP6wh2mWz?usp=sharing 

Do let me know if there is any further query

[Irwan]

hello @mmvohra 

you can do this in various ways, but here i show how i usually do.

you can do this in either calculated column or measure.

1. using calculated column

- create a new table to summarize those ID less than 3000.

Summarize =
SUMMARIZE(
    FILTER(
        'Work',
        'Work'[Dif KM_1]=0||'Work'[Dif KM_1]<=2000
    ),
    'Work'[ID],
    'Work'[ConsolidatedID],
    'Work'[Distance (km)],
    'Work'[Date],
    'Work'[Price],
    'Work'[Person Type],
    'Work'[Name],
    'Work'[Link]
)

- create a new calculated column with following DAX for price different.

Price Different =
var _Previous = 
MAXX(
    FILTER(
        'Summarize',
        'Summarize'[ConsolidatedID]=EARLIER('Summarize'[ConsolidatedID])&&
        'Summarize'[Date]<EARLIER('Summarize'[Date])
    ),
    'Summarize'[Price]
)
Return
IF(
    not ISBLANK(_Previous),
    'Summarize'[Price]-_Previous
)

 - create a new measure for sum value of price different.

Sum Column = SUM('Summarize'[Price Different])

2. using measure

- create a new measure with following DAX for calculating price different.

Price Different =
var _Previous = 
MAXX(
    FILTER(
        ALL('Work'),
        'Work'[ConsolidatedID]=SELECTEDVALUE('Work'[ConsolidatedID])&&
        'Work'[Date]<SELECTEDVALUE('Work'[Date])&&
        'Work'[Dif KM_1]<=2000
    ),
    'Work'[Price]
)
Return
IF(
    not ISBLANK(_Previous),
    SELECTEDVALUE('Work'[Price])-_Previous
)

- create a new measure with following DAX for sum value.

Sum Measure =
SUMX(
    FILTER(
        ALL('Work'),
        'Work'[ConsolidatedID]=SELECTEDVALUE('Work'[ConsolidatedID])&&
        'Work'[Dif KM_1]<=3000
    ),
    [Price Different]
)

- create a slicer for consolidateID filter and KPI visual for showing sum value (either card or KPI or any visual you want)

Hope this will help.

Thank you.

[mmvohra]

I want total sum but it should be dynamic based on the value of parameter. 
Calculated columns and table are of no use as parameter does not work on them.
I want a measure that can dynamically filter the table based on dif KM_1<=parameter value condition and then find the sum of difference in price meeting the criteria.