Forum Discussion

mmvohra's avatar
mmvohra
Icon for Helper II rankHelper II
6 months ago
Solved

Need help regarding fine measure formulation

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 

Need Help
Show and Tell

14 Replies

      • Irwan's avatar
        Irwan
        Icon for Super User rankSuper User
        6 months ago

        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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 months ago

    Hi,

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

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    6 months ago

    Hi mmvohra 

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

  • v-karpurapud's avatar
    v-karpurapud
    Icon for Community Support rankCommunity Support
    6 months ago

    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.

  • v-karpurapud's avatar
    v-karpurapud
    Icon for Community Support rankCommunity Support
    6 months ago

    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.