Recognizing Inconsistent value with Date Changes

[Anonymous]

@Greg_Deckler you might be able to assist with this one. By the way, the solution you created for the previous questions was phenominal.

I have a column that recognizes if there is a discrepency between value in the "Pricing Unit" column by looking at the "Unique Contract/Material/Vendor" colunmn. For instance, Contract 5994393 has an error with Material 20's Pricing Unit. Under this Contract's Material 20, the Pricing Unit shows as both 1000 and 1 and generates a "2" in the Pricing Error column. However, looking at the Contract Start / Contract End columns you can see that the error was corrected on 1/13/2020. This was an error from 1/1/2020 - 1/12/2020 but was caught, and corrected; now the date ranges are 1/13/2020-12/31/9999 for the corrected entry. A column needs to show that this error has been corrected.

Similarly, Contract 47389 has a pricing unit that is inconsistent that hasn't been caught yet. The Contract End date goes to Year 9999 and the Pricing unit is inconsistent. A column needs to show that this is an error that has not yet been corrected.

Unique Contract/Material/Vendor	Contract	Material	Vendor	Pricing Unit	Measure	Contract Start	Contract End	Pricing Unit Error Formula (>1 = Error)
5994393_20_Brick International	5994393	20	Brick International	1	CAS	1/13/2020	12/31/9999	2
5994393_20_Brick International	5994393	20	Brick International	1	CAS	11/20/2019	12/31/2019	2
5994393_20_Brick International	5994393	20	Brick International	1000	CAS	1/1/2020	1/12/2020	2
47389_10_Wood Supplier	47389	10	Wood Supplier	1	Per	1/1/2019	12/31/2019	2
47389_10_Wood Supplier	47389	10	Wood Supplier	100	Per	1/1/2020	12/31/9999	2

[v-alq-msft]

Hi, @Anonymous 

Based on your description, you may create a measure as below.

Result = 
var _ID = SELECTEDVALUE('Table'[Contract])
var _Ifhaserror = 
CALCULATE(
    DISTINCTCOUNT('Table'[Pricing Unit]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Contract] = _ID
    )
)

var _start = 
CALCULATE(
    SUM('Table'[Pricing Unit]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Contract Start] = 
        CALCULATE(
            MIN('Table'[Contract Start]),
            FILTER(
                ALL('Table'),
                'Table'[Contract] = _ID
            )
        )
    )
)

var _end = 
CALCULATE(
    SUM('Table'[Pricing Unit]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Contract Start] = 
        CALCULATE(
            MAX('Table'[Contract Start]),
            FILTER(
                ALL('Table'),
                'Table'[Contract] = _ID
            )
        )
    )
)

return
IF(
    _Ifhaserror=1,
    "no error",
    IF(
        _Ifhaserror>1&&_start=_end,
        "this error has been corrected",
        IF(
            _Ifhaserror>1&&_start<>_end,
            "an error has not yet been corrected"
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-alq-msft Thanks for the assistance! This is catching errors and non-errors but I am receiving false positives when it comes to errors that have been corrected.  In the measure, why did you focus on Contract Start and not Contract End? Within Contract End, if the year is 9999 then I know that this is the current/present price being shown (doesn't mean it's correct). Also, the Contract Start date, once corrected, will never be the date as before (formula line: Ifhaserror.1&&_start=_end). It will be the very next day.

I have switched every instance of 'Table'[Contract] with 'Table'[Unique Contract/Material/Vendor] as it zeros in on what exactly needs to match up. 

[v-alq-msft]

Hi, @Anonymous 

Please try to use the following measure to see if it can help.

Result = 
var _ID = SELECTEDVALUE('Table'[Unique Contract/Material/Vendor])
var _Ifhaserror = 
CALCULATE(
    DISTINCTCOUNT('Table'[Pricing Unit]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Unique Contract/Material/Vendor] = _ID
    )
)

var _start = 
CALCULATE(
    SUM('Table'[Pricing Unit]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Unique Contract/Material/Vendor]=_ID&&
        'Table'[Contract End] = 
        CALCULATE(
            MIN('Table'[Contract End]),
            FILTER(
                ALL('Table'),
                'Table'[Unique Contract/Material/Vendor] = _ID
            )
        )
    )
)

var _end = 
CALCULATE(
    SUM('Table'[Pricing Unit]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Unique Contract/Material/Vendor]=_ID&&
        'Table'[Contract End] = 
        CALCULATE(
            MAX('Table'[Contract End]),
            FILTER(
                ALL('Table'),
                'Table'[Unique Contract/Material/Vendor] = _ID
            )
        )
    )
)

return
IF(
    _Ifhaserror=1,
    "no error",
    IF(
        _Ifhaserror>1&&_start=_end,
        "this error has been corrected",
        IF(
            _Ifhaserror>1&&_start<>_end,
            "an error has not yet been corrected"
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-alq-msft This has been a great help and is providing me with a great deal of insight. However, I have struggling to put this information in any useful graph or visual. Even though I can display the formula result sin a column, it doesn't act as one....meaning, I can't create a slicer off the returned values, bar charts, etc.

I have tried changing the formula to be recognized either as a "custom column" so it can be used in the above manner, but with no luck.

Is there a way to adapt the above solution into a "Custom Column" or in such a way that the results can be dis[played visually? 

[v-alq-msft]

Hi, @Anonymous 

There may be a way to create a custom column to achieve your requirement. Unfortunately I am not good at Power Query. I hope someone else could help you.

Best Regards

Allan