Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))))
Solved! Go to Solution.
Nevermind, I have solved it 🙂
filtr as: DATE( YEAR(MAX('1604'[Date_Created])) ;MONTH(MAX('1604'[Date_Created])) ;1) <= '1604'[Date_Created]
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
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))))
Nevermind, I have solved it 🙂
filtr as: DATE( YEAR(MAX('1604'[Date_Created])) ;MONTH(MAX('1604'[Date_Created])) ;1) <= '1604'[Date_Created]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |