Apply quarterly % to monthly results . Please help!

[tinkertrust]

I have market share data that’s released quarterly that I want to use in relation to monthly results, assuming that the quarterly share % applies to all 3 months in that quarter.

I can see three options:

1. Create a column in the monthly table that pulls the quarterly share % from the relevant quarter. If this is the best option, can anyone help me with this? Can you help me do this in Power Query rather than DAX so it's more reproducible?

2. Writing measures so they pull in the right quarterly share % when needed. For example:

= SUM per month * quarterly share % (where month is in that Yr and Qtr)

I have a calendar table that would help with this conversion, but don’t know what the formula would look like. Help?

3. Break out the current quarterly share % into a new table that’s organised by month. This is workable, but seems like its adding unnecessary weight to the model.

[v-juanli-msft]

Hi @tinkertrust

Could you share some data and expected results with me, I believe all three options can be achieved.

Also, I don't know how to use quarterly share % for monthly results, could you give me an example?

Best Regards

Maggie

[Seward12533]

#2 is your best bet. Key is to have a date table that has both month and quarter values in it that is linked to month and quarter in your tables respectively. When using in visuals or measures you need to use aggregate function like AVERAGE (the average quarterly target will be the target)

If you want use option one the function in Dax would be to add a column and use LOOKUPVALUE but option 2 is better. You could do in power query with a merge query but it’s more work.