Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AlvaroLeonardoN
Frequent Visitor

Transform from quarterly to monthly Power bi

 

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? 

 

AlvaroLeonardoN_0-1709929346605.png

 

1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

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:

vzhouwenmsft_0-1710494818808.png

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

vzhouwenmsft_1-1710494894599.png

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.

View solution in original post

2 REPLIES 2
v-zhouwen-msft
Community Support
Community Support

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:

vzhouwenmsft_0-1710494818808.png

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

vzhouwenmsft_1-1710494894599.png

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
Super User
Super User

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.