Forum Discussion

Sdhn420's avatar
Sdhn420
Helper IV
5 years ago
Solved

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

 
 
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    5 years ago

    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:

     


    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.

8 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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.

    • Sdhn420's avatar
      Sdhn420
      Helper IV
      5 years ago

      parry2k 

      Month    Avg Sales       Qty 

      July        300                 250

      Aug       300                 600

      Sep       300                 400

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    5 years ago

    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's avatar
        Mikelytics
        Resident Rockstar
        5 years ago

        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.

         

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    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:

     


    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.

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    5 years ago

    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.