Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Matching problem: Same dates receive same value

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. 

test1212.jpg

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!

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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:

1.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

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:

1.PNG

 

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Dear @v-jayw-msft , 

Thank you so much for your immediate and wonderful support! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.