VLOOKUP Report Stock Evolution POWER BI

[Anonymous]

Dear, 

I have to report the evolution of stock between two identical files that changes from one week to another.

To do this, I perform a VLOOKUP in Excel from the last file obtained 'Claims - Current Week' which is larger than the file 'Claims - Previous week' from the previous week.

I therefore compare the extraction of the previous week with that of the current week from a key named "CLAIM ID".

At the end of this, I filter on the result of my search column V in 'Claims - Current Week' on the N/A field. Only the N/A fields have to be reported.

That's why I would like to reproduce this VLOOKUP in PowerBi to be able to automate it.

Could you please help me with this. 

Thank you.

[v-alq-msft]

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

[v-alq-msft]

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.

[amitchandak]

@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

[harshnathani]

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

[Fowmy]

@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