Current month 31 and 30

[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?

[Icey]

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

[netanel]

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

[Icey]

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.

[netanel]

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?

[smpa01]

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

[netanel]

Hey @smpa01 @VahidDM 

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

[smpa01]

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

[VahidDM]

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

AVERAGEX(_A,[SNUS])

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/

[netanel]

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