Forum Discussion

netanel's avatar
netanel
Post Prodigy
4 years ago
Solved

Current Month

Hey All!

 

I have this Measure:

 
Net USD average per Day =
AVERAGEX(
(VALUES('Date'[Date])),
CALCULATE(SUM('Revenues DB'[Net USD]))
)
 
My problem is that always in the current month there is a minus on the last day of the month

(This is how the data collection) attaches an image
I want the average to ignore this last day so that it does not hurt the average


What to do?

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, netanel 

    If you want to exclude negative and null values, then try the following measures:

    Net USD average per Day3 = 
AVERAGEX(
	except(VALUES('Date'[Date]),SUMMARIZE(FILTER(ALLSELECTED('Revenues DB'),'Revenues DB'[Net USD]<=0),[Date])),
	CALCULATE(SUM('Revenues DB'[Net USD]))
)

    Result:

     

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

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

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    netanel , Try like

     

    Net USD average per Day =
    AVERAGEX(
    (VALUES('Date'[Date])),
    CALCULATE(SUM('Revenues DB'[Net USD]), filter('Date', 'Date'[Date] <> eomonth('Date'[Date],0) ))
    )

    • netanel's avatar
      netanel
      Post Prodigy
      4 years ago

      Hey amitchandak 

      Thanks for all your help!

       

      It does not work, I tried to do something similar
      The average really jumped
      The reason is that there are minuses every day of the month, meaning that every day consists of a scheme of negative and positive numbers

      They should not be ignored
      But from the last day of the month yes should be ignored

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        netanel , In the above I have ignore last date of the month  , do want ignore only negative value on last day

         

        Net USD average per Day =
        AVERAGEX(
        (VALUES('Date'[Date])),
        CALCULATE(SUM('Revenues DB'[Net USD]), filter('Revenues DB', 'Revenues DB'[Date] <> eomonth('Revenues DB'[Date],0)  && 'Revenues DB'[Net USD] >0 ))
        )

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, netanel 

     

    Could you please consdier sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.
    It makes it easier to give you a solution.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

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

      • v-angzheng-msft's avatar
        v-angzheng-msft
        Community Support
        4 years ago

        Hi, netanel 

        If you want to exclude negative and null values, then try the following measures:

        Net USD average per Day3 = 
AVERAGEX(
	except(VALUES('Date'[Date]),SUMMARIZE(FILTER(ALLSELECTED('Revenues DB'),'Revenues DB'[Net USD]<=0),[Date])),
	CALCULATE(SUM('Revenues DB'[Net USD]))
)

        Result:

         

        Please refer to the attachment below for details. Hope this helps.

         

         

        Best Regards,
        Community Support Team _ Zeon Zheng

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

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, netanel 

    Whether the value of the selected date is less than or equal to 0. The above measure will filter all dates where the value is less than or equal to 0.
    Check your posting above again, does it filter only the last day of the latest month while keeping the negative values of the other dates?

     

     

     

     

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, netanel 

     

    May I ask if your problem has been solved? Is the above post helpful to you?

    If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.😀

     

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


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

    • netanel's avatar
      netanel
      Post Prodigy
      4 years ago

       

      Hi v-angzheng-msft amitchandak 

       

      Yes your and amitchandak answer
      They helped me a lot but unfortunately the problem has not been solved yet
      I'm working on preparing the data and clearing it for sending you in collaboration
      In your answer the problem is as I have already mentioned that there are months where I get BLANK and there are months that looking good
      In a amitchandak  answer everything is well received
      But on average the months SUM to quartered and the quarters to years something that has already happened to me in the past and amitchandak  solved it her :  https://community.powerbi.com/t5/DAX-Commands-and-Tips/Daily-AVG-Sum/m-p/2177815#M50665
      The solution is also in the one drive i share (You can see there the good formula and the bad formula of the average)

      Anyway I will mark your answer as a solution Soon if I do not find a way to solve it Thanks very much you helped me  allot thanks!