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.
Hey All!
I have this Measure:
(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?
Hi @netanel ,
Do you want to ignore all records of last day of a month, whether it's positive or negative?
In addition, how do you deal with negative values that appear at other times?
Best Regards,
Icey
Hey @Icey
Thanks for your help
Do you want to ignore all records of last day of a month, whether it's positive or negative?
Only if it is negative
And only if it's in the current month
In addition, how do you deal with negative values that appear at other times?
On the other days of the month I want both the negative and the positive
Thanks Again
Hi @netanel ,
How about the below one, modified from @smpa01 's measure:
Measure =
AVERAGEX (
FILTER (
ADDCOLUMNS (
'Revenues DB',
"test",
IF (
CALCULATE (
LASTDATE ( 'Date'[Date] ),
ALLEXCEPT ( 'Date', 'Date'[Year], 'Date'[Month] )
)
= CALCULATE ( MAX ( 'Revenues DB'[Date] ) )
&& 'Revenues DB'[Net USD] < 0,
0, -------------------------------------------replace the negative values with 0
'Revenues DB'[Net USD]
)
),
[test] <> 0 ----------------remove that day when calculating average (day - 1, if you don't want "day -1",delete this filter step)
),
[test]
)
In addition, in your scenario, "current month" means "the month that today is in", or "the month you selected"?
Based on your report, it is "the month you selected", right?
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Icey
In addition, in your scenario, "current month" means "the month that today is in", or "the month you selected"?
the month that today is in
Only in the example is this selected month
It seems to work well
But when I bring it to my live PBI
I get a much lower number
Say 2,000 instead of 2,000,000
Do you have any idea why?
@netanel if you have a Date table like this
Can you please check if one of these measures give you what you need? pbix is attached
_m1 =
VAR _0 =
AVERAGEX (
( VALUES ( 'Date'[Date] ) ),
CALCULATE ( SUM ( 'Revenues DB'[Net USD] ) )
)
VAR _1 =
MAX ( 'Revenues DB'[Date] )
VAR _2 =
CALCULATE (
CALCULATE (
LASTDATE ( 'Date'[Date] ),
ALL ( 'Date' ),
VALUES ( 'Date'[Year] ),
VALUES ( 'Date'[Month] )
)
)
RETURN
IF ( _1 = _2, _0 * -1, _0 )
_m2 =
AVERAGEX (
ADDCOLUMNS (
'Revenues DB',
"test",
IF (
CALCULATE (
LASTDATE ( 'Date'[Date] ),
ALLEXCEPT ( 'Date', 'Date'[Year], 'Date'[Month] )
)
= CALCULATE ( MAX ( 'Revenues DB'[Date] ) ),
'Revenues DB'[Net USD] * -1,
'Revenues DB'[Net USD]
)
),
[test]
)
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
@netanel are you looking for something like this?
Measure = AVERAGEX(ADDCOLUMNS (
'Revenues DB',
"test",
IF (
CALCULATE (
LASTDATE ( 'Date'[Date] ),
ALLEXCEPT ( 'Date', 'Date'[Year], 'Date'[Month] )
)
= CALCULATE ( MAX ( 'Revenues DB'[Date] ) ) && 'Revenues DB'[Net USD]<0 ,
'Revenues DB'[Net USD] * -1,
'Revenues DB'[Net USD]
)
),[test])
HI @netanel
Can you add a sample of your data?
BTW, try this if there is a relationship between Date and Revenues DB tabels :
Measure=
Var _A = Addcolumns(VALUES('Date'[Date]),"SNUS",SUM('Revenues DB'[Net USD]))
return
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hey @VahidDM !
Thanks for the help
Yes There is relationship between Date and Revenues DB
The formula you added brings me an annual average she SUM months to quarters and quarters to year and then makes an average, so the average is really big
I look for a daily average even when going up for a quarter and a year
So unfortunately I can not check if it is correct
Another thing I can not create Data for demo Because is sensitive data and it is a lot to change and hide
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |