Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
netanel
Post Prodigy
Post Prodigy

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

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?

 

 








Did I answer your question?
Mark my post as a solution!
Appreciate your Kudos!

Connect on Linkedin
linkedin.com/in/netanel-shriki
1 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()))

)

View solution in original post

5 REPLIES 5
ALLUREAN
Solution Sage
Solution Sage

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




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




amitchandak
Super User
Super User

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

)

 

 

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

 








Did I answer your question?
Mark my post as a solution!
Appreciate your Kudos!

Connect on Linkedin
linkedin.com/in/netanel-shriki

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

)

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







Did I answer your question?
Mark my post as a solution!
Appreciate your Kudos!

Connect on Linkedin
linkedin.com/in/netanel-shriki

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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