cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Post Prodigy

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?

Mark my post as a solution!

1 ACCEPTED SOLUTION
Community Support

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 13
Community Support

Hi, @netanel

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.

Post Prodigy

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
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!

Mark my post as a solution!

Community Support

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?

Community Support

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.

Post Prodigy

Unfortunately I have not yet reached a solution

I would be very happy for your help!

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

Mark my post as a solution!

Community Support

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.

Post Prodigy

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?

Mark my post as a solution!

Community Support

Hi, @netanel

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

Post Prodigy

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

Mark my post as a solution!

Super User

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

Post Prodigy

Hey @amitchandak

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

Mark my post as a solution!

Super User

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

Post Prodigy

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?

Mark my post as a solution!

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors