Combining two measures in PowerPivot for use in graphs / pivot tables

[Anonymous]

Hi everyone,

I am looking to combine two measures based on the year so that I can show them as one line in a graph and as a single measure in a pivot table.

One measure calculates something for all of 2019 and 2020, then I have another measure calcualting the same thing but from different sources for 2021 onwards. Currently I have to drag the two measures into graphs seperately and have them in different pivot tables with different filters to achieve the correct result. 

Is there a way to say if year is 2019/2020, use Measure A, if 2021 onwards, use measure B, so that once combined it would funcion like a single measure pulling the correct data based on the year?

I am using PowerPivot not PowerBI so cant create addtional tables, so ideally it would be a DAX measure.

Hopefully quite simple but can't seem to find a solution.

Thanks all

Alex

[edhans]

You need to remodel your data. You may have two data sources that have 2019 and 2020 in one source, and 2021 in another, but that is what Power Query is for. You combine those into a single FACT table so you don't have this issue. This will scale too. Another source for 2022? Just transform and combine in Power Query. 2023, 2024? Same.

If you use DAX to do this, it will keep getting worse. Can you imagine the DAX gymnastics you'd have to use to get it to combine 4-6 FACT tables that are really the same data just different years?

[Anonymous]

Hi there, 

Thanks for your repsonse. It is not that the data is fro 2 sources, it is that the calcuation is differnet for past years than current years. So next year one cacluation will apply to the 2019, 2020, 2021 data, but 2022 will be different. Essentially it is becuase the full year calc is differnet to the ongoing monthly calc, however the result is being presented as the same measure.

Appreacite any more advice.

Thanks,

Alex

[edhans]

You need a single measure that will handle both. The measure can get the year from the filter context provided by the axis of your chart, then do the appropriate calculation for the current year vs past years. There is really no way for me to help further without some data and a model to play with. I'm not sure I could give a good answer with just the fact table of your sales. A link to a PBIX file with fake data would be very helpful.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Anonymous]

Hi,

Thanks again for your help. I have tried to pull out some data but there are so many tables and measures it all falls apart because of all the hierarchys, so have tried to break it down below in a table as I'd like the PivotTable to look (and thus the chart). 

Calc 1: As it is the end of the year it is full year revenue divided by December costs (which are an annual figure such as staff costs) and is used as the end of year benchmark so other dates are filtered out, calculated basically as follows: = sum(Costs[Amount]) / sum(Revenue[YTD Amount])

Calc 2 is different due to end of year revenue not being available and is: = (sum(Costs[Amount])/12)  /  sum(Revenue[MTD Amount])

The main difference is that as the costs being reported each month are annualised, for calc 2 they are divided by 12 and compared to one months revenue, whilst for the december values its just the full amount.

	Dec 19	Dec 20	Jan 21	Feb 21	Mar 21	Apr 21	May 21
Entity 1	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2
Entity 2	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2
Entity 3	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2
Entity 4	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2
Entity 5	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2
Entity 6	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2
Entity 7	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2
Entity 8	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2
Entity 9	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2
Entity 10	Calc 1	Calc 1	Calc 2	Calc 2	Calc 2	Calc 2	Calc 2

I can't seem to find a way to get the measure to use a different calc for either:

1. When the month is December, use calc 1, if not use 2

2. When year is not current / isnt 2021 or similar use Calc 1 (it wouldnt matter that the non December months were out as they are never used for benchmarking in this way) .

Hopefully that explains it better than before, and thanks for looking.

Alex