cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors