Redistribute sales across the year based on quarter

[Karma777]

Hi Everyone, Good day!

I am stuck with a situation where I need to write DAX to redistribute sales values based on the date range in quarter. I will try to explain it as easily as possible.

Please see the below table ( lets call it 'Info') with sample data. Here the month is in YYYYMM format so basically 202101 means Jan 2021 and corresponding values in "sales" column is the data for that month.

Here for simplicity the total sales is 1000 for the whole year. Now based on quarter, the sales value need to be resdributed. The formula is :
For Q1, 10% of Total Sales
For Q2, 20% of Total Sales

For Q3, 30% of Total Sales

For Q4, 40% of Total Sales
Example : Here Total Sales = 1000. So Q1 sales = 10% of 1000 = 100. Now there are there months in Q1 - Jan, Feb, March. This value for Q1 would be equally distributed for each month i.e., 33.33 each for Jan, feb, March.
Based on this logic, I need to create a new calculated column ( lets call it 'Sales New') which should have values like shown below.

Can somebody please help in writing DAX for this calculated column. Thanks in advance!

[v-xiaotang · Accepted solution]

Hi @Karma777 

Please try this column,

Sales New = 
var _total= 1000
var _q1=0.1
var _q2=0.2
var _q3=0.3
var _q4=0.4
var _q= VALUE(RIGHT('Table'[Month],2))
return SWITCH(TRUE(),
_q>=1 && _q<4,_total*_q1/3,
_q<7,_total*_q2/3,
_q<10,_total*_q3/3,
_q<13,_total*_q4/3
)

Best Regards,

Community Support Team _Tang

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