Help on summing up totals from today thru end of the month

[Anonymous]

Hello -  I know I can accomplish what I need thru using various filters.    However, what I really need is a measure. 

I have a table of Orders....orders that have not yet shipped.  

I simply need to sum those orders starting from Today....and each day thru the end of the current month.    So, for example, 

$100  Sept 14

$323  Sept 15

$96    Sept 16 etc etc....

So, the cumulative value of the orders from today thru the end of this month  

Thanks!

[v-juanli-msft]

Hi @Anonymous 

It is possible.

Please refer to the useful links:

https://www.mssqltips.com/sqlservertip/5085/data-forecasting-and-analytics-with-power-bi-desktop/

https://databear.com/forecasting-in-power-bi/

https://skillsmatter.com/skillscasts/9075-powerbi-meetup

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi  -  I'm not sure people are understanding my question. 

I am not trying to forecast.    I already have the forecast numbers from our CRM software.  

I am trying to add the total, from Today to the end of the month, of these numbers.    I do not want to include anything prior to Today, and I do not want to include numbers from the next month.   Just the total from Today thru to the end of this month.  

[Anonymous]

Try this:

ROM Orders = CALCULATE( SUM(Table[Orders]) , Table[Date] >= today(), Table[Date] <= EOMONTH(TODAY(),0))

You would need to have a date column in your data set.

[Anonymous]

Well, the last effort did not work...it gave me the same value all the way down the table for each day.   

[Anonymous]

It would do because that is what you asked for unless I have misunderstood..

What do you need the measure to do? You said sum the orders from Today through to the end of the month..

[Anonymous]

Sorry!  I had the wrong column.   Thank you! 

Two follow up questions: 

1.  I see that EOMonth is being used, but there is no exact equivalent for the Year.     How would you sum up those same totals from Today to the end of the Year? 

2.  Is there a way to make the formula dynamic so rather than having to have a filter applied, the formula automatically knows what current Month we are in?    That way I do not need to manually always select the filter for the current Month.  

Many thanks, 

[Anonymous]

Hi

1.  For end of the year you can specify the month and day to give you the end of the year:  DATE(YEAR(TODAY()),12,31)

2. The formula I sent is dynamic as it uses the TODAY() function. So whatever day the calculation runs it with display the calculation for that current month.  You shouldn't need to use any external filters.

[Anonymous]

2. The formula I sent is dynamic as it uses the TODAY() function. So whatever day the calculation runs it with display the calculation for that current month.  You shouldn't need to use any external filters.

[Anonymous]

@Anonymous  Thanks so much for your help!

One final simple question, and I will close this out.  

Rather than using a filter on my Rest of Month measure to filter by probability, such as 70%, or 90%  (the potential of if a deal will close or not), is there not a way to embed this "filter" in the RestofMonth measure you helped me with?   

For example, create a measure that is my "RestofMonth 90%".    I have a "Probability" column that list all of the percents relative to potential deals in our CRM system.    But 90% is one that I would like to use a lot, and not always have to have a filter selected on 90%.     Thanks again!

[Anonymous]

You just add that as another filter in the calculate fucntion. 

ROM 90% Orders = CALCULATE( SUM(Table[Orders]) , Table[Date] >= today(), Table[Date] <= EOMONTH(TODAY(),0), Table[Probability] = 0.9)

You can add as many filters you want to a calculate function as long as they are based on a column. Filters based on measures are a little more tricky.

[amitchandak]

Try something like

Sales till date = 
Var _Todays_date=CALCULATE(maxx(ALLSELECTED('Date'[Date Filer]),'Date'[Date Filer]))

Var   Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))

return
Sale_till_tody

// _Todays_date can be used in place of maxx(Sales,Sales[Sales Date].[Date]) as per need

[Anonymous]

Thanks, but I am not sure if this what I needed.  

I need the forecast amounts FROM today......thru the end of this current month.     In other words, we have forecast from our CRM data that go thru the end of this month.   I want to be able to total them, each day, from the current day...to the end of that same current month.