cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Bring data only to the present day

Hi All!

I have a Divide DAX that divides the money by date
And so I get an excellent daily average.

I connect  Keepfilters to it of a date
To bring me only up to the date of the present day

Now I'm in trouble
My data does not always reach the present day
Sometimes the current day is the 18th of the month and the data is only updated until the 12th of the month.

What happens is that the formula divides the money by 18 instead of 12 and that's how I get a wrong average
I try to solve this with Values but always in the current month I have minus money on the 31st of the month

The right thing to do is:
To bring data by the day they arrive at DB
how do I do it?

This is the formula that works for me right now:

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

Mark my post as a solution!

1 ACCEPTED SOLUTION
Community Support

Hi， @netanel ；

First, it's not very clear about your table structure, if you want the divisor of January to be 31? February is 28...and so on. if so ,you could try

``````COUNTX ( FILTER ( 'Date', MONTH ( [Date] ) = MONTH ( TODAY () ) ), [Date] )
``````

If not, Can you provide a simple file and screenshot of the result you want to output? It makes it easier to give you a solution.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Community Support

Hi， @netanel ；

First, it's not very clear about your table structure, if you want the divisor of January to be 31? February is 28...and so on. if so ,you could try

``````COUNTX ( FILTER ( 'Date', MONTH ( [Date] ) = MONTH ( TODAY () ) ), [Date] )
``````

If not, Can you provide a simple file and screenshot of the result you want to output? It makes it easier to give you a solution.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@netanel , Try a measure like

Net USD AVG =

AverageX(Values( 'Date'[Date] ), CALCULATE( SUM( 'DB 2022'[Net USD] ),
keepfilters('Date'[Date] < TODAY())) )

Post Prodigy

@amitchandak  Thanks

The best solution so far!
You really are a Master.
There is a small problem
I also have provisions in Value and it comes in one amount once a month
So I made Divide to divide it by 31 days in a month
Otherwise he makes me a minus of the whole amount of the provisions
Say in January the provisions are 4000
I want to divide by 31
It is possible?
Because the whole formula is excellent except for the provisions

Thanks allot @amitchandak

Mark my post as a solution!

Super User

@netanel , I have this solution to distribute value on the month when they are month level, see if that can help

Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

Post Prodigy

I tried this is not the right direction in my context.

Anyway I thought of another solution
I will stick with my formula:

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

Is there a possibility that he will not bring me days that have BLANK or zero?

Because if so then no matter what day I am in the month
It will be considered to me only what my data brings

Mark my post as a solution!

Post Prodigy

I'll try now and see what it's going for me
Is there an option in your formula to just do a Divide?

Thanks @amitchandak

Mark my post as a solution!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors