DAX forecast measure - handling blanks in forecast

[werdinka]

Hi Guys, I am trying to calculate strange "forecast" for client which should show sum of values for current month till the end of the year - I've built this function and it works okey for non blank values. It seems that if there is no real data for example March in forecast value from February is shown instead of blank(for example if service was not provided this month) . Could you please advice how to handled blank values in forecast? 

 

[v-juanli-msft]

Hi @werdinka

Could you give an example of your dataset showing on this table(or matrix?) and show me how you calculate the measure"forecast"?

Which data is orginal data and which are from your measure?

Best Regards

Maggie

[werdinka]

Hi, I hopev this helps:

[Ashish_Mathur]

Hi,

Share the link from where i can download your Excel file.

[werdinka]

Hi, 

below there's a link. Thanks in advance!

https://uploadfiles.io/lnxpe

[Anonymous]

Hi,

I'm lookig the solution for the same appreciae if you could share the related File.

REgards

[Ashish_Mathur · Accepted solution]

Hi,

Here's the result i got.  You may download the Excel file from here.

Hope this helps.

[werdinka]

Ashish!

Thank you so much - this is perfect!

[Ashish_Mathur]

You are welcome.

[werdinka]

Ashish, one more thing. I would like grand total to show sum of my calculated field -( I am talking about the formula I wrote in file previously) -  Why they are so different ? (The last show result from sum formula) 

[Ashish_Mathur]

Hi,

Your question is not clear.  Do you want the Grand total appearing in column N of the image that i posted to be the summation of the numbers from column B:M?

[werdinka]

Hi Ashih, 

As I mentioned before I need also to calculate "forecast" basing on last available data for the month ( I need to show it till the end of the year) . I need it to show correct grand totals for two or more dimesnison- in this case for category and for product 

Test2:=CALCULATE((CALCULATE([sumofamount],filter(all('Calendar'),Calendar[Date]=MAX(Table1[Period])))),filter(all('Calendar'),Calendar[Date]<=MAX(Calendar[Date])))

As you see below - I need grand total (and subtotals ) to show sum of shown values (as in row Total I need)

you can find my example here:  https://ufile.io/efav3

[Ashish_Mathur]

Hi,

I have tried quite hard but have not found a solution to this problem.  I suggest you start a new thread and share the link with me here so that i can also subscribe to it as well.  May be someone else has a solution.