Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Decreasing by code and date

Hi, 

 

May I ask you if there is a possible way to do the same but for the decrease?

If the code appears today but no longer tomorrow I want it to reflect it as a negative.

 

Here I attached an example: 

 

For example the code 888890 had value 72 for March 2nd, but it didn't  have any values on March 3rd So i want the measure to return a -72 On March 3rd.  An d the same case for March 4th and 5th returning a -125. So that the measure now instead of giving me the total sum for thos positive values I want it to give me only the sum for the negative ones. 

 

Hope this is possible!

 

Thanks in advance. 

 

@Ashish_Mathur 

Need Help
Tutorial Requests
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, Please try this measure:

    Measure = 
VAR a =
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            ALLSELECTED ( 'Table'[Date] ),
            'Table'[Date]
                = CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Date] < MIN ( 'Table'[Date] )
                            && 'Table'[Date Code] IN DISTINCT ( 'Table'[Date Code] )
                    )
                )
        )
    )
RETURN
    IF (
        a = BLANK (),
        IF ( MAX ( 'Table'[Values] ) = BLANK (), BLANK (), 0 ),
        SUM ( 'Table'[Values] ) - a
    )

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

2 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    According to your description, Please try this measure:

    Measure = 
VAR a =
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            ALLSELECTED ( 'Table'[Date] ),
            'Table'[Date]
                = CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Date] < MIN ( 'Table'[Date] )
                            && 'Table'[Date Code] IN DISTINCT ( 'Table'[Date Code] )
                    )
                )
        )
    )
RETURN
    IF (
        a = BLANK (),
        IF ( MAX ( 'Table'[Values] ) = BLANK (), BLANK (), 0 ),
        SUM ( 'Table'[Values] ) - a
    )

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi