Lookup past 1 month and past 2 month data from different table

[Mars3442]

Hi all,

I want to look up data from different table

Example of Stock table

Example Sales Table

Expected table

Stock January 2023 add column past 1 month sales = Sales December 2022

Stock February 2023 add column past 1 month sales = Sales January 2022

All of Stock March 2023 add column past 1 month sales = Sales February 2022

Stock January 2023 add column past 2 month sales = Sales November 2022

Stock February 2023 add column past 2 month sales = Sales December 2022

All of Stock March 2023 add column past 2 month sales = Sales January 2022

Please help me to solve this problem using DAX or M Language

Thank you..

[Mars3442]

I try to use this DAX but it not works

Measure = CALCULATE( SUMX( 'SALES' , 'SALES' [SALES]), DATESINPERIOD( 'SALES' [Date], MAX( ' STOCK' [Date]), -1, MONTH))

Measure = CALCULATE( SUMX( 'SALES' , 'SALES' [SALES]), DATEADD( 'SALES' [Date], -1, MONTH))

[AilleryO]

Hi,

Do you have a Date Table ? If yes it should be easy with CALCULATE + DATEADD functions (with -1 MONTH and -2 MONTH), or maybe DATESINPERIOD.

Then finally your visual will be build with the dates coming from the date table, this one being related to the 2 other tables (sales and stock).

Hope it helps otherwise tell us what's not clear.

[Mars3442]

Yes I have date table like this

Date

1 Dec 2022

1 Nov 2022

1 Jan 2023

1 Feb 2023

1 March 2023

8 March 2023

15 March 2023

Please help me with the DAX or using power query

Thankyou

[AilleryO]

Hi,

You cannot have a "real" date table being like that.

A date table got (at least) a column with dates, all dates being uniques and contiguous (1st jan, 2nd jan, 3rd jan....), more details :

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

It must be marked as date table (right click on table), and related to fact table

Without a proper date table, you might encounter many results being empty (null).

Please read above informations and add to your model a "real" date table and then it should work easily.

Let us know