Need Help with LOOKUP of a value from a column and the value is in another column of the same table

[rahuldas_vgm]

I have the following Columns:

ID      Sales Amt.     Sales Month       Previous Month

A1       1000               June                    May

A2        2000              April                    March

A1         3000             May                    April

A2         4000             March                 February

I need to make a column which would lookup previous month column's value in sales month and return the sales amount. A lot like:

ID      Sales Amt.     Sales Month       Previous Month   Previous Month Sales Amount

A1       1000               June                    May                            3000

A2        2000              April                    March                         4000  

A1         3000             May                    April                             -

A2         4000             May                    April                            2000

Tried Measures and calculated column but failing to get the desired output.

Any help would be 

[mahoneypat]

Here is a calculated column expression that should work.  However, how do you handle Jan with Dec prev month?  You should consider converting your Month columns to dates, adding a Date table to your model, so you can then do this type of analysis with time intelligence measures.

PrevMon Sales =
VAR prevmon = Amt[Previous Month]
VAR thisID = Amt[ID]
RETURN
    SUMX (
        FILTER ( FILTER ( Amt, Amt[Sales Month] = prevmon ), Amt[ID] = thisID ),
        Amt[Sales Amt.]
    )

Pat

[rahuldas_vgm]

Hello Pat,

Thank You for your suggestion. This really brought me to think about the January Month and taking it backward. I have now created Present Date and previous month date column from query edit. I did come across timeintelligence analysis posts here before and will definitely try to resolve the problem.

Also, it would be of great help if you could guide me with this part, in case I'm unable to follow the posts properly.

Thank you again!! 

[v-yiruan-msft]

Hi @rahuldas_vgm ,

Whether your problem has been resolve? If no, you can create a measure as below to get the sales for previous month:

Previous Month Sales Amount = 
VAR _premonth =
    SELECTEDVALUE ( 'Table'[Previous Month] )
VAR _selid =
    SELECTEDVALUE ( 'Table'[ID] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales Amt.] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = _selid
                && 'Table'[Sales Month] = _premonth
        )
    )

In addition, you can refer the solution in the following links to get it.

direct query previous month

Get value for previous month

Calculate last month value in DAX with Power BI

If your problem has been resolved, could you please mark it as Answered? It will help the others in the community find the solution easily if they face the same problem with yours. Thank you.

Best Regards

[rahuldas_vgm]

Hello Rena,
Thank you for your help. The result is not showing me any values but there is no error message. The type of data in the column sales is in words(text) if i were to return a text, what should i replace the SUM function with?

Thank you again for your help, looking forward to your reply. 

[v-yiruan-msft · Accepted solution]

Hi @rahuldas_vgm ,

You can use MAX function to replace the original SUM function just as shown in below screenshot:

Previous Month Sales Amount =

VAR _premonth =

SELECTEDVALUE ( 'Table'[Previous Month] )

VAR _selid =

SELECTEDVALUE ( 'Table'[ID] )

RETURN

CALCULATE (

MAX ( 'Table'[Sales Amt.] ),

FILTER (

ALLSELECTED ( 'Table' ),

'Table'[ID] = _selid

&& 'Table'[Sales Month] = _premonth

)

)

If what you want to get is a column, you can create a calculated column as below:

Column = 
    CALCULATE (
        MAX ( 'Table'[Sales Amt.] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = EARLIER('Table'[ID])
                && 'Table'[Sales Month] = EARLIER('Table'[Previous Month])
        )
    )

If the above ones are not working, please provide more sample data with Text format and your expected result with screenshot or special examples. Thank you.

Best Regards

[rahuldas_vgm]

Sorry, I had forgotten to say that the current month is being selected through a slicer. So, now I realized that your solution works perfectly but only when the slicer has been completely removed or the current month and the respective previous month is selected from the slicer.
I will definitely mark this as a solution to the question I had posted but it would really be of great help if you could let me know:

1. How to create a column which would have the values of the measure that your solution is providing.

2. How to make visuals view the previous month data on single month selection instead of two consecutive month selection.

Thanks a ton!

[VahidDM]

Hi @rahuldas_vgm 

Try this to add a new column:

Column = LOOKUPVALUE('Table'[Sales Amt.],[Sales Month],[Previous Month])

Output:

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

Appreciate your Kudos!!

[rahuldas_vgm]

The column Previous Month was created in query edit for calculated column by using Dateadd(SalesMonth,-1,Month). Is that the reason it is giving me the error. Also if it is possible to get previous month sales value from the column Sales Month itself as per the values inside the column?

Thanks again for all of the wonderful suggestions and help!

[rahuldas_vgm]

Thank You @VahidDM 

I tried lookupvalue in create column but it gives an error " A table of multiple value was supplied where a single value was expected".

😞

Thank You for your suggestion!!