A formula is calculated for the number of days in a month up to the present day

[netanel]

HI All!

Please I would be happy to help here!

I have this formula:

Net USD try =

CALCULATE(

    DIVIDE( SUM( 'Revenues DB'[Net USD] ), COUNTROWS( 'Date' ) ),

    'Revenues DB'[Date] < TODAY()

)

The formula divides by the number of days in the month of say January so it divides by 31
I want her to divide the number of days in a month only until the current day
For example we are on December 27th so you will only split in 27 days and not in 31st

Can you help please?

[ALLUREAN]

Hi, @netanel 

Can you check this one?

https://www.dropbox.com/s/329t8nqy5kujue1/number%20of%20days%20in%20a%20month%20up%20to%20the%20pres...

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

[amitchandak]

@netanel , try one of the three

Net USD try =

CALCULATE(

DIVIDE( SUM( 'Revenues DB'[Net USD] ), day(TODAY()) )

)

Net USD try =

CALCULATE(

DIVIDE( SUM( 'Revenues DB'[Net USD] ), distinctcount( 'Date'[Date] ) ),

filter('Date', 'Date'[Date] < TODAY()))

)

Net USD try =

CALCULATE(

DIVIDE( calculate(SUM( 'Revenues DB'[Net USD] ), datesmtd('Date'[Date])), day(TODAY()) )

)

[netanel]

Hi @amitchandak 

Both formulas are not good
The first brings a bad daily average, in months she looks fine
The second in general arranges them from the largest to the nest and distorts all the numbers

link to my PBIX:

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

thanks

[amitchandak · Accepted solution]

@netanel , what is the expected number, to me this seems fine

Net USD average per Day created by chenwuz = 
calculate(divide(SUM('Revenues DB'[Net USD]),DISTINCTCOUNT('Date'[Date])),DATESMTD('Date'[Date]),FILTER('Revenues DB', 'Revenues DB'[Date]<TODAY()))

or this should work

Net USD try =

CALCULATE(

DIVIDE( SUM( 'Revenues DB'[Net USD] ), distinctcount( 'Date'[Date] ) ),

filter('Revenues DB', 'Revenues DB'[Date] < TODAY()))

)

[netanel]

@amitchandak @ALLUREAN 

This is th right measure:

Net USD try 4 =

CALCULATE(

DIVIDE( SUM( 'Revenues DB'[Net USD] ), COUNTROWS( 'Date' ) ),

keepfilters( 'Date'[Date] < TODAY())

)

Thanks to everyone for the direction and guidance
It helped me a lot!