Forum Discussion

skitovich's avatar
skitovich
Helper I
6 years ago
Solved

Trouble with calculation(help)

Hello everyone. In the screenshot one, I show my test pattern. The task is to calculate the values ​​according to the formula below: The SUM"Count_of_sales" + Sum "Count of Calls" -100. It is neces...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try this:

    constant = 
IF (
    'Table'[Index]
        = CALCULATE (
            MIN ( 'Table'[Index] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] = EARLIER ( 'Table'[Date] )
                    && 'Table'[Manager] = EARLIER ( 'Table'[Manager] )
            )
        ),
    100 + DATEDIFF ( MIN ( 'Table'[Date] ), 'Table'[Date], DAY ),
    0
)

    The result shows:

     

    Best Regards,

    Giotto

skitovich's avatar
skitovich
Helper I
6 years ago

This will not solve my problem. This formula that you wrote adds the value 100, only to manager1, and I need to add a number to all unique managers (manager1, manager2, manager3). I also pointed out that managers can fill out a daily report several times. I need 1 manager to be assigned a constant only 1 time per day. This is difficult, and therefore turned to the forum.

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

Hi,

 

Please try this:

constant = 
IF (
    'Table'[Index]
        = CALCULATE (
            MIN ( 'Table'[Index] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] = EARLIER ( 'Table'[Date] )
                    && 'Table'[Manager] = EARLIER ( 'Table'[Manager] )
            )
        ),
    100 + DATEDIFF ( MIN ( 'Table'[Date] ), 'Table'[Date], DAY ),
    0
)

The result shows:

 

Best Regards,

Giotto