LTM calculation based on multiple master records

powerbiAG — Wed, 01 Feb 2023 13:46:27 GMT

Hi All,

We have a scenario wherein a company has on-boarded other businesses and all the details (includng the acquired month) are available in Subsidiary master dimension table. Sample data below...

Subsidiary Id	Subsidiary Name	Acquisition Month
1	Business 1	Feb-21
2	Business 2	Dec-19
3	Business 3	Oct-18

We have Sales transaction tables historic and current (with same structure)

Subsidiary Id	Sales Month	Sales (Historic)
1	Jan-20	100
1	Feb-20	200
1	Mar-20	120
2	Sep-19	100
2	Aug-19	300
2	Nov-19	120
3	Jul-18	100
3	Apr-18	100
3	Sep-18	120

Subsidiary Id	Sales Month	Sales (Current)
1	Jan-22	100
1	Feb-22	200
1	Mar-22	120
2	Jan-22	100
2	Feb-22	300
2	Mar-22	120
3	Jan-22	100
3	Feb-22	100
3	Mar-22	120

Requirement is to calculate the last 12 months Sales (Historic) data (for each subsidiary) based on the "acquisition month" in the subsidiary master.

expected sample output..

Subsidiary Id	Last 12 Months Sales Historic
1	420 (100 + 200 + 120)
2	520 (100 + 300 + 120)
3	320 (100 + 100 + 120)

the sample data has been provided only for 3 months for ease of understanding.

Any help would be highly appreciated.

Thanks.

topic LTM calculation based on multiple master records in DAX Commands and Tips

LTM calculation based on multiple master records