VLOOKUP Report Stock Evolution POWER BI

Anonymous 

You can use the MERGE TABLE option in Power Query to replicate the VLOOKUP operation.

Please refer to these articles, let me know if you face difficulties.

https://www.myonlinetraininghub.com/excel-power-query-vlookup
https://excelchamps.com/blog/vlookup-power-query/

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn 

Hi Anonymous ,

You can also have a look at the video which explains the types of join and how to merge.

https://www.youtube.com/watch?v=PNdxy0c1Shg

https://www.youtube.com/watch?v=qXH4WjCykLc

Regards,

Harsh Nathani

Anonymous , for this week vs last week refer

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

week calendar

tps://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

Hi, Anonymous 

I assume that you want to compare 'Claims-Current Week' with 'Claims-Last Week' for specific 'Claim ID'. I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is a relationship between two tables. You may create calculated columns and measures as below.

Calculated Column:
YearWeek = YEAR('Calendar'[Date])*100+WEEKNUM('Calendar'[Date])

Measure:
Claims-Current Week = 
var _currentweek = YEAR(TODAY())*100+WEEKNUM(TODAY())
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Claim ID],
    "CurrnetWeek",
    var _claimid = [Claim ID]
    return
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Claim ID] = _claimid&&
            RELATED('Calendar'[YearWeek]) = _currentweek
        )
    )
)

return
SUMX(
    tab,
    [CurrnetWeek]
)

Claims-Last Week = 
var _currentweek = YEAR(TODAY())*100+WEEKNUM(TODAY())
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Claim ID],
    "LastWeek",
    var _claimid = [Claim ID]
    return
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Claim ID] = _claimid&&
            RELATED('Calendar'[YearWeek]) = 
            CALCULATE(
                MAX('Calendar'[YearWeek]),
                FILTER(
                    ALL('Calendar'),
                    'Calendar'[YearWeek]<_currentweek
                )
            )
        )
    )
)
return
SUMX(
    tab,
    [LastWeek]
)

Result:

If i misunderstand your thoughts, please show us some sample data and  expected result with OneDrive for business. Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

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

Hi, Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan