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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Yoo
New Member

How to make a DAX Commnad for Sales Reuslt+MKT Fcst

https://drive.google.com/file/d/1I2kagPkXb5juYgkxIrIh1ap14DL_xCdH/view?usp=sharing 

 

Hello, I am studying Power BI on my own and applying it to my work, but there are many restrictions using Power BI with the in-house DB, so if I knew DAX well, it will be solved.
Pic1Pic1

Data establishes weekly sales plans for each week as above, but due to changes in past sales result, it also manages real sales result data by creating an additional Planweek called 999952. I made New Measures as below, and I would like to make a New Measure that combines Sales result and Market Fcst. I already mada Result+MKT Fcst, but the value is correct on only a monthly basis and is not correct on yearly, quaterly. Second problem, I want to see the sales result in 2011 when I select Plan week 220205, 220206.

 

MKT_FCST = CALCULATE(sum(Sheet1[Qty]),Sheet1[Measure] in {"Market Fcst"})

Result = CALCULATE(sum(Sheet1[Qty]),Sheet1[Measure]="Allocation",Sheet1[W0] in {"Result_F"},all(Sheet1[Plan_week]))

Reuslt+MKT Fcst = Var Check = AVERAGE(Sheet1[Target_week])

Var Plan = [MKT_FCST]

Var Result = Indicator[Result]

Return if(AVERAGE(Sheet1[Plan_week])>Check,Result,Plan)


I couldn't change the original structure of the table, so I'd like to solve it with New Measure. Please help me

Question: 1. How to make Result + MKT Fcst for displaying  the MKT_FCST value other than Result_F when Plan week is larger than Target week

2. When selecting 202205, 202206 only, output Result + MKT Fcst in 2021

 

Pic2Pic2

Pic3Pic3

1 REPLY 1
v-zhangti
Community Support
Community Support

Hi, @Yoo 

 

Not sure if my understanding is correct, please check if this is the result you want.

MKT_FCST 2 = CALCULATE(sum(Sheet1[Qty])
                    ,Sheet1[Measure] ="Market Fcst", Sheet1[W0] <>"Result_F")
Reuslt+MKT Fcst = Var Check = AVERAGE(Sheet1[Target_week])
                Var Plan = [MKT_FCST]
                Var Result = Indicator[MKT_FCST 2]
                Return if(AVERAGE(Sheet1[Plan_week])>Check,Result,Plan)

vzhangti_0-1652432330251.png

Is this the result you want for question 1? I didn't understand question 2, can you provide a sample diagram of the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.