Transform from quarterly to monthly Power bi

[AlvaroLeonardoN]

Hello everyone,

I appreciate your help, I am new to this, how do I transform the data from the "pay" column to the "incentive" variable that is quarterly to monthly? 

[v-zhouwen-msft · Accepted solution]

Hi @lbendlin ,thank you for your quick reply, I thought of other options, the following are my additions.

Hi @AlvaroLeonardoN ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a column named ‘Index’

Index = SWITCH([Month],
"Jan",1,
"Feb",2,
"Mar",3,
"Apr",4,
"May",5,
"Jun",6,
"Jul",7,
"Aug",8,
"Sep",9,
"Oct",10,
"Nov",11,
"Dec",12)

2. Use the following DAX expression to create a column

Column = 
VAR _a = 'Table'[Index]
VAR _b = _a - MOD(_a - 1,3)
VAR _c = CALCULATE(MAX('Table'[Pay]),FILTER('Table','Table'[Index] = _b))
RETURN 
IF(ISBLANK(_c),BLANK(),IF('Table'[Concept] = "Incentive",_c / 3 , 'Table'[Pay]))

3. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You may not need to do that if the value is always divided by three.  In your monthly visuals, display a third of the quarterly value for each month.