Current Month

[netanel]

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?

[v-angzheng-msft]

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]

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!

[v-angzheng-msft]

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]

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.

[netanel]

Hey @v-angzheng-msft @amitchandak 

Thanks Allot for your helps

Unfortunately I have not yet reached a solution

Attaches access to clean data

I would be very happy for your help!

https://1drv.ms/f/s!AonyYI-TdspHgUgReR5uqvnKLTCF

[v-angzheng-msft · Accepted solution]

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.

[netanel]

Hey @v-angzheng-msft 

Thanks so mach!

I invested a lot of time in it

Yes! This is exactly what I was looking for

But a small problem I take it to my real PBI
In many cases BLANK appears Do you have any idea why this is happening?

[v-angzheng-msft]

Hi, @netanel 

Where the blank values appear? please consider attaching screenshots or sample data to let me know what you are dealing with

[netanel]

Hi @v-angzheng-msft 

The BLANK Appears in different cases
By clicking on one quarter and 4th quarter
By clicking on certain months
And on a daily level

[amitchandak]

@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]

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]

@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 ))
)

[netanel]

@amitchandak 

now I get it
The average jumps because the formula sums me up all days of the month
And if I rise in a hierarchy to a quarterly level all the months add up again
You have solved this problem for me in the past
In this post:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Daily-AVG-Sum/m-p/2177815#M50665

So your formula is good, I just need it without the Sum 
like As in the post attached here

You can Help me whit that?