Bring data only to the present day

[netanel]

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?

Please save me
I've tried everything already

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

[v-yalanwu-msft · Accepted solution]

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.

Looking forward to your reply!

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.

[amitchandak]

@netanel , Try a measure like

Net USD AVG =

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

[netanel]

@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 

[amitchandak]

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

[netanel]

@amitchandak 

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

And now I ask
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

[netanel]

@amitchandak 

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