Matching problem: Same dates receive same value

[Anonymous]

Hi everyone,

I have a table as below and try to find solution for a matching problem. I would like to get the same value in [Value] for each month in [Date], if [Date] in the year 2019. This mean, all rows with [Date] = 01.01.2019 will get [Value] = 100, and all rows with [Date] = 01.02.2019 will get [Value] = 250, and so on. [Date] of 2018 will stay in the data. 

My initial approaches are LOOKUPVALUE(), and CALCULATE() together with ALLEXCEPT(). However they don't work.

So I hope that I can receive any idea from you.

Thank you and wish all you a nice christmas time!

[Anonymous · Accepted solution]

Hi @Anonymous ,

If i understand you correctly, EARLIER() function might be helpful for you.

Please refer to the measure below and see if the result achieve your expectation.

Column = 
IF (
    FORMAT ( 'Table'[date], "YYYY" ) = "2019",
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER (
            'Table',
            FORMAT ( 'Table'[date], "YYYYMM" )
                = FORMAT ( EARLIER ( 'Table'[date] ), "YYYYMM" )
        )
    ),
    'Table'[value]
)

Result would be show as below:

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Dear @Anonymous , 

Thank you so much for your immediate and wonderful support!