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

Bring only to the present day

Hello everyone!

And thanks to all the helpers

I have this formula:

Net USD average per Day =

AVERAGEX(

(VALUES('Date'[Date])),

CALCULATE(SUM('Revenues DB'[Net USD])

))

I want her to bring me data but only to the present day
And not the rest of the month as in the picture

What to do?

Attached is a link to my files:

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

Help 1.JPG

 

 

 

 

 

 

 

 

 

 

 

Thanks!








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

Hi @netanel ,

 

Net USD average per Day real one =
AVERAGEX(
(VALUES('Date'[Date])),
CALCULATE(SUM('Revenues DB'[Net USD])
))

 

you can just add calculate outside the formlua. 

Net USD average per Day real one =
CALCULATE(
AVERAGEX(
(VALUES('Date'[Date])),
CALCULATE(SUM('Revenues DB'[Net USD])
)
),'Revenues DB'[Date]<TODAY())
 

Best Regards

Community Support Team _ chenwu zhu

View solution in original post

12 REPLIES 12
amitchandak
Super User
Super User

@netanel , Try like

 

Net USD average per Day =

calculate(

AVERAGEX(

(VALUES('Date'[Date])),

CALCULATE(SUM('Revenues DB'[Net USD])

)) , 'Date'[Date] =today())

Hey @amitchandak 

If you put your formula in the files I attached

you find that she's not working ...

 

netanel_0-1640248613240.png

 

 








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

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

@netanel , try like

Avg of all dates on a filtered date, if you need less that that we need add logic

 

Net USD average per Day = 
CALCULATE( AVERAGEX(
(VALUES('Date'[Date])),
CALCULATE(SUM('Revenues DB'[Net USD]),all('Date')
)),filter(('Date'),'Date'[Date]= TODAY()))

@amitchandak 

I tried, now he brings me only the present day, and not the rest of the days back

 

netanel_0-1640250182092.png

one more thing 

When I change the sign to smaller from the current day all the information is the same,
Do you know why?

help 2.JPG

 








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

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

Hi @netanel ,

 

I have a menthod you can try. let's call it mark tag.

create a measure.

Measure =
IF( SELECTEDVALUE( calender[Date] ) > TODAY(), BLANK(), 1 )

 

Then put the measure in the filters pane and set items as 1 as following screenshot show.

vchenwuzmsft_0-1640681383733.png

Result:

vchenwuzmsft_2-1640681459996.png

 

vchenwuzmsft_1-1640681431606.png

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-chenwuz-msft 

The formula works great and indeed brings only to the present day in the graph.
But my formula in the Value column still calculates the minuses?
Because I can not put your formula in the Card so the calculation of the average comes out incorrect ...








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

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

Hi @netanel ,

 

Can you share your pbix file without sensitive data if you need more help.

Best Regards

Community Support Team _ chenwu zhu

Hi @v-chenwuz-msft 

 

I also shared above at the beginning of the post, sharing again here:

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

 

The formula and idea work great on the graph, I just want them to work on the card as well
i dont now how to connect the two formulas

 

My Formula:

Net USD average per Day =
AVERAGEX(
(VALUES('Date'[Date])),
CALCULATE(SUM('Revenues DB'[Net USD])
))
 
Your Formula:
Net Trying = if(SELECTEDVALUE('Date'[Date])> TODAY(),BLANK(),1)
 

 








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

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

Hi @netanel ,

code:

Net USD average per Day created by chenwuz =
CALCULATE(
    DIVIDE( SUM( 'Revenues DB'[Net USD] ), COUNTROWS( 'Revenues DB' ) ),
    'Revenues DB'[Date] < TODAY()
)

Result:

vchenwuzmsft_0-1640772274760.png

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-chenwuz-msft 

Your formula is good, I corrected a bit that it would suit me better, you just miss something small in it
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 it be done?

Net USD try =

CALCULATE(

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

    'Revenues DB'[Date] < TODAY()

)

 

My previous formula divides by the number of days that have values and because on the 31st of the month I have a minus so if we are on the 27th of the month it will divide by 28 days
Because in 27 days there are values and also in 31

I literally go in circles with this Measure

previous formula:

Net USD average per Day =

AVERAGEX(

(VALUES('Date'[Date])),

CALCULATE(SUM('Revenues DB'[Net USD])

))

 

 

 

please help

 

Thanks!!!








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

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

Hi @netanel ,

 

Net USD average per Day real one =
AVERAGEX(
(VALUES('Date'[Date])),
CALCULATE(SUM('Revenues DB'[Net USD])
))

 

you can just add calculate outside the formlua. 

Net USD average per Day real one =
CALCULATE(
AVERAGEX(
(VALUES('Date'[Date])),
CALCULATE(SUM('Revenues DB'[Net USD])
)
),'Revenues DB'[Date]<TODAY())
 

Best Regards

Community Support Team _ chenwu zhu

Hi @v-chenwuz-msft 

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

This is the right measure:

Net USD try  =
CALCULATE(
DIVIDE( SUM( 'Revenues DB'[Net USD] ), COUNTROWS( 'Date' ) ),
keepfilters( 'Date'[Date] < TODAY())
)

 








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.