Forum Discussion

panula's avatar
panula
Frequent Visitor
8 years ago
Solved

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

Need Help
  • panula's avatar
    panula
    8 years ago

    Nevermind, I have solved it :) 

     

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

3 Replies

    • panula's avatar
      panula
      Frequent Visitor
      8 years ago

      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's avatar
        panula
        Frequent Visitor
        8 years ago

        Nevermind, I have solved it :) 

         

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