Calculating replacement sales in a measure?

[Lygral63]

I need help to set up a measure that will calculate future replacement sales based on sales history:

A certain product has been sold in the volume illustrated in the sales table: Sales. In the following years the products will be replaced according to the distribution in the table: ReplDist. I need to calculate the replacement volume in any years like illustrated in the lower table.

Is it possible to create a measure that will calculate the total replacement in any given year? The example is a bit simple, but explains what I am trying to do. I would like to avoid using calculated columns as I will have to work with data overing more than 100 years of sales and would like to avoid hundreds of columns.

Thanks in advance.

Sales			ReplDist
Year	Sales		After x Years	% Replaced
2016	1200		1	5%
2017	1300		2	20%
2018	1600		3	50%
2019	1500		4	20%
2020	1700		5	5%
Year	Sales	Replacement	Replacement	Replacement	Replacement	Replacement	Total Replacement
2016	1200	60					60
2017	1300	240	65				305
2018	1600	600	260	80			940
2019	1500	240	650	320	75		1285
2020	1700	60	260	800	300	85	1505
2021	xxxx	xxx	65	320	750	340	xxxx
2022	xxxx	xxx	xxx	80	300	850	xxxx
2023	xxxx	xxx	xxx	xxx	75	340	xxxx
2024	xxxx	xxx	xxx	xxx	xxx	85	xxxx
2025	xxxx	xxx	xxx	xxx	xxx	xxx	xxxx

[Lygral63]

@Greg_Deckler  It seems that the measures work fine if I use the full sales history (which covers a number of countries), but if I add a filter to only look a the sales history for specific countries, it seems that the measure is still calculating on the basis of the full sales history (ie. disregarding filters). Is that correct and what should I do to modify the replacement measure to allow filtering of eg. sales history? 

Replacement =

VAR __MinYear = MIN('Year (Horizontal)'[Value])

VAR __Sales = MAXX(FILTER(ALL(SalesHistory),[Year]=__MinYear),[Sales])

VAR __StartYear = MIN('Year (Vertical)'[Value])

VAR __Diff = __StartYear - __MinYear + 1

VAR __Percent = MAXX(FILTER('X Years',[X_Years] =__Diff),[Repl_Dist_Annual])

RETURN

__Sales * __Percent

ReplacementTotals =

VAR __ColumnTotal =

ADDCOLUMNS(

'Year (Vertical)',

"__Replacement",[Replacement]

)

VAR __RowTotals =

ADDCOLUMNS(

'Year (Horizontal)',

"__Replacement",[Replacement]

)

RETURN

SWITCH(TRUE(),

HASONEVALUE('Year (Vertical)'[Value]) && HASONEVALUE('Year (Horizontal)'[Value]),[Replacement],

HASONEVALUE('Year (Horizontal)'[Value]),SUMX(__ColumnTotal,[__Replacement]),

SUMX(__RowTotals,[__Replacement])

[Lygral63]

@Greg_Deckler All clear. Thanks.

[Greg_Deckler]

@Lygral63 Not really, the first measure returns the correct value at the line level. The second measure uses the first measure. You use the second measure in your visual. The first measure is there used behind-the-scenes.

[Greg_Deckler · Accepted solution]

@Lygral63 - OK, one last version, fixes both row and column totals. See below PBIX, the final measure includes the first measure definition and then this one:

ReplacementTotals = 
    VAR __ColumnTotal = 
        ADDCOLUMNS(
            '1_Years',
            "__Replacement",[Replacement]
        )
    VAR __RowTotals =
        ADDCOLUMNS(
            '1_Years1',
            "__Replacement",[Replacement]
        )
RETURN
    SWITCH(TRUE(),
        HASONEVALUE('1_Years'[Value]) && HASONEVALUE('1_Years1'[Value]),[Replacement],
        HASONEVALUE('1_Years1'[Value]),SUMX(__ColumnTotal,[__Replacement]),
        SUMX(__RowTotals,[__Replacement])
    )

[Lygral63]

@Greg_Deckler  Thanks again. This has been such a great help. I have been struggling with this for some time, so looking forward to test the measure and the principle with the real data. 🙂

[Lygral63]

@Greg_Deckler  If I understand you correctly, I will now need to combine the two measures (see below). If it is not asking too much, could you show me how you would make the correct combination? Any help highly appreciated ...

Replacement =
VAR __MinYear = MIN('1_Years1'[Value])
VAR __Sales = MAXX(FILTER(ALL('1_Sales'),[Year]=__MinYear),[Sales])
VAR __CurrentYear = MIN('1_Years'[Value])
VAR __Diff = __CurrentYear - __MinYear + 1
VAR __Percent = MAXX(FILTER('1_ReplDist',[After x Years]=__Diff),[% Replaced])
RETURN
__Sales * __Percent

ReplacementTotals =
VAR __ColumnTotal =
ADDCOLUMNS(
'1_Years',
"__Replacement",[Replacement]
)
VAR __RowTotals =
ADDCOLUMNS(
'1_Years1',
"__Replacement",[Replacement]
)
RETURN
SWITCH(TRUE(),
HASONEVALUE('1_Years'[Value]) && HASONEVALUE('1_Years1'[Value]),[Replacement],
HASONEVALUE('1_Years1'[Value]),SUMX(__ColumnTotal,[__Replacement]),
SUMX(__RowTotals,[__Replacement])
)

[Greg_Deckler]

@Lygral63 - How about this? See updated PBIX below. Totals need some work.

Replacement = 
    VAR __MinYear = MIN('1_Years1'[Value])
    VAR __Sales = MAXX(FILTER(ALL('1_Sales'),[Year]=__MinYear),[Sales])
    VAR __CurrentYear = MIN('1_Years'[Value])
    VAR __Diff = __CurrentYear - __MinYear + 1
    VAR __Percent = MAXX(FILTER('1_ReplDist',[After x Years]=__Diff),[% Replaced])
RETURN
    __Sales * __Percent

For Totals, see my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Lygral63]

@Greg_Deckler  This is just great. It will save me from creating hundreds of measures. Not entirely sure what is going on with the totals, but as you say, a bit of work is still needed. Once the totals are working, I will definitely try the measure (probably with a few adjustments) on the real dataset. Hope it works.

Thanks again.

[Greg_Deckler]

@Lygral63 Might be other ways of doing this, like having a single measure and having a matrix with "Years" in the columns but here is a method using multiple measures. Feels like I am missing some of the data model here. See PBIX attached below sig.

[Lygral63]

@Greg_Deckler Thanks a lot. This is already a good step step forward. Using a measure for every year could work, but since I will be working with probably more than 100 years of data, I would have to create lots of measures and add all of them in another measure to get to the total. Working with just one measure, would of course be a lot easier, but I struggle to find a way to set it up. If you have an idea how that could be done, it would be fantastic...:-)