cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sdhn420
Helper IV
Helper IV

Constant Value

Hi, 

I have calculated average sales in the last 3 months. 

 VAR DURATION = DATESINPERIOD('d_Calender'[Date],LASTDATE(inv[RptMth]),-3,MONTH) RETURN CALCULATE([SalesQty],DURATION))/3
 
I need this value to be displayed from Jul. Currently, the value is changing for each month but i need this to be constant. 
 
How can i do this?
 

Month    Avg Sales       Qty 

July        300                 250

Aug       300                 600

Sep       300                 400

 
 
1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

Hi @Sdhn420 ,


According to my understanding, the reason why the test formula you used is reporting an error is because format("30.06", "dd.mm.yyyy") returns a text type, not a date type, so an error will be reported. Try the following formula:

v-henryk-mstf_0-1612436039304.png

 


If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.


Best Regards,
Henry


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

8 REPLIES 8
Mikelytics
Resolver III
Resolver III

@Sdhn420 

 

you did not replace the complete text I asked for.

 

Please replace the whole text FORMAT("30.06.2020","dd.mm.yyyy")

by

"30.06"

 

so that you have:

 

VAR 
DURATION = DATESYTD('d_Calender'[Date],"30.06.")
RETURN 
CALCULATE(AVERAGE([SalesQty]),DURATION)

 

Best regards

Mikelytics

 

Did I solve your request? Please mark my post as solution.

Appreciate your Kudos.

 

 

v-henryk-mstf
Community Support
Community Support

Hi @Sdhn420 ,


According to my understanding, the reason why the test formula you used is reporting an error is because format("30.06", "dd.mm.yyyy") returns a text type, not a date type, so an error will be reported. Try the following formula:

v-henryk-mstf_0-1612436039304.png

 


If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.


Best Regards,
Henry


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

Mikelytics
Resolver III
Resolver III

@Sdhn420 ,

 

Can you please try the following:

 

 VAR 
DURATION = DATESYTD('d_Calender'[Date],FORMAT("30.06.2020","dd.mm.yyyy"))
RETURN 
CALCULATE(AVERAGE([SalesQty]),DURATION)

 

Best regards

Mikelytics

 

Did I solve your request? Please mark my post as solution.

 

Appreciate your Kudos.

@Mikelytics its giving an error: 

16121248599962946859453123039259.jpg

HI  @Sdhn420 

 

sorry, my mistake.

 

Please replace FORMAT("30.06.2020","dd.mm.yyyy") by "30.06"

 

Best regards

Mikelytics

 

Did I solve your request? Please mark my post as solution.

 

Appreciate your Kudos.

 

@Mikelytics still gives error

Sdhn420_0-1612264875182.png

 

parry2k
Super User III
Super User III

@Sdhn420 can you post sample data and expected output, when you said "constant", what value you like to see as constant. Provide a bit more context/business rules.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

Month    Avg Sales       Qty 

July        300                 250

Aug       300                 600

Sep       300                 400

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.