cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tanat_inc
Helper I
Helper I

Use January as target

Hi community, 

My company decided to use Jauary 2020 as Target. We aim to make every month revenue reach the same level as it was on Janurary 2020. I have two problems regarding this issue to consult :

1. The way I wrote meause with DateAdd(), it's provide the same variation of revenue on daily basic.
>> I want this target to be flat 5.14 Million per day , not vary daily as the true sale on January.

 

2. When I select period more than one month , Target does not increased further than 159.55 Million.

>> I expects that if I select two month period, target should return 2 * 159.55 Million.

Measure I wrote = target  (in measure talbe) :

target =

VAR today_day = DAY(MAX(d_date[Date]))
VAR today_month = MONTH(MAX(d_date[date]))
VAR today_year = YEAR(MAX(d_date[Date]))
VAR month_diff = (-1 * (today_month - 1)) + ((2020-today_year) * 12)

RETURN
CALCULATE([Total Revenue],
DATEADD('d_date'[Date],month_diff,MONTH),
d_date[day] <= today_day,
d_date[month_no] = 1,
d_date[year] = 2020


Dummy data et found in link below :
https://www.dropbox.com/s/rxtpzsg3yyw2e4b/dummy_2020.pbix?dl=0

 

tanat_inc_0-1622274129145.png

 

ps: sorry for repost since I have no time to create simplified dummy data set earlier;

1 ACCEPTED SOLUTION
gboppana
New Member

@tanat_inc  -

For #1, can you clarify if you want the TARGET to be same as the 1 Jan 2020 always? Or do you want to display a constant value of  5.14 Million per day?



For #2, you can get the desired result using Summarize and SUMX as below.

TargetSum =
SUMX(
SUMMARIZE( d_date,
d_date[year_month],
"TargetTotal", [target]
),
[TargetTotal]
)

View solution in original post

3 REPLIES 3
Eyelyn9
Community Support
Community Support

Hi @tanat_inc ,

 

You said 5.14 is average revenue of Jnauary 2020, but how to get it? Based on my test, the result I got is 5.35M

Avg of 2020-01 = CALCULATE(SUM(bill_info[net_amt]) / DISTINCTCOUNT('bill_info'[bill_date]),FILTER('bill_info',YEAR([bill_date])=2020 && MONTH([bill_date])=1))

average of 202001.PNG

 Could you please tell me more details?

 

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

gboppana
New Member

@tanat_inc  -

For #1, can you clarify if you want the TARGET to be same as the 1 Jan 2020 always? Or do you want to display a constant value of  5.14 Million per day?



For #2, you can get the desired result using Summarize and SUMX as below.

TargetSum =
SUMX(
SUMMARIZE( d_date,
d_date[year_month],
"TargetTotal", [target]
),
[TargetTotal]
)

View solution in original post

Thanks for your time, and sorry for unclear explanation/

5.14 is average revenue of Jnauary 2020, no a constant. Also if it's calculated from avarage, it should be able to filtered to show target of each department.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.