cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
panula Frequent Visitor
Frequent Visitor

RunRate for current month (starting date for current month)

Hi, 

I have stucked at probabbly small isuee in calculation of runrate. 

 

I used this formula below which works fine, howeve I need to calculate Runrate which will not change with any slicer and it will show only current month so I need to replace in filter "'1604'[Date_Created] " to date which will represent starting of current month. So for September 1.9.2018, october 1.10.2018 etc. I tried max(STARTOFMONTH however it doesnt work. 

 

And of course it has to be value from column "date_created" I have allready tried DATE(YEAR(TODAY()); MONTH(TODAY()); 1) , however in this case it won't be as bottom line of filter and I will select all values to maximum value, not values from starting of current month.

 

Any idea how to solve it? I am really lost in it currently. 

 

Thnaks! 

 

RunRate = (CALCULATE (
COUNT( '1604'[LWE_ORDER_NUMBER] );
FILTER (
ALLSELECTED('1604');
'1604'[Date_Created] <= MAX ( '1604'[Date_Created])
)
)/DAY(LASTDATE('1604'[Date_Created]))*(DAY(EOMONTH(today();0))))

1 ACCEPTED SOLUTION

Accepted Solutions
panula Frequent Visitor
Frequent Visitor

Re: RunRate for current month (starting date for current month)

Nevermind, I have solved it Smiley Happy 

 

filtr as: DATE( YEAR(MAX('1604'[Date_Created])) ;MONTH(MAX('1604'[Date_Created])) ;1)  <= '1604'[Date_Created] 

3 REPLIES 3
Super User
Super User

Re: RunRate for current month (starting date for current month)

Sample data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


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

Proud to be a Datanaut!


panula Frequent Visitor
Frequent Visitor

Re: RunRate for current month (starting date for current month)

Hi Greg,

thanks for detailed info about posting. 

 

I am sending also sample data in 3 columns. 1 is ORDER Number (number of order), second is Date_Created (whole timestamp) and 3rd is only date (created from timestamp). 

 

What I need is calculate runrate (as below - linear version) without clicking on any time filter. So if I have sample data till beginning of September I need to calculate runrate for whole September, so 106 order, divided by 1 (number of all days which run in September) multiply by 30 (number of days in September). 

 

I am struggeling still with filter as I can select only all orders (15023) and not only 112 from September in order to calculate runrate only for September. 

 

Link for GoogleSheet with data:  https://docs.google.com/spreadsheets/d/14ns8libtGNGHquTCey8gtMJx8CjLOg6OnIDYblaU_xA/edit?usp=sharing

 

Hope it is clear now, if not please write me, 

Regards,

 

 

RunRate = (CALCULATE (
COUNT( '1604'[Order NUMBER] );
FILTER (
ALLSELECTED('1604');
'1604'[Date_Created] <= MAX ( '1604'[Date_Created])
)
)/DAY(LASTDATE('1604'[Date_Created2]))*(DAY(EOMONTH(today();0))))
panula Frequent Visitor
Frequent Visitor

Re: RunRate for current month (starting date for current month)

Nevermind, I have solved it Smiley Happy 

 

filtr as: DATE( YEAR(MAX('1604'[Date_Created])) ;MONTH(MAX('1604'[Date_Created])) ;1)  <= '1604'[Date_Created]