Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Finding Date of First Non Zero Value

Hello All ,

 

Thank you for supporting a new user. 

 

I am creating a visual to compare actual or forecast launch dates (first sales date) for new products compared to budgeted launch date (budgeted first sales date).

 

I have the following data for every product:

 

1 - Budgeted sales by Month last 2 years. Months prior to first sale (i.e. Launch) have zero sales value.

2 - Actual sales by Month last 2 years. Months prior to first sale (i.e. Launch) have zero sales value.

3 - Forecast sales by Month 2 years into the future. Month prior to first sale (i.e. Launch) have zero sales value.

 

I would like to create a column in my visual that has as output the Month/Year of the first sale according to budget sales.

 

And a second column in my visual that has as output the Month/Year of the first sale according to 

(a) actual if it has already launched (i.e. has recorded non zero sales in the past or

(b) date according to forecast (i.e. has not launched or recorded first non sero sales yet).

 

Idea being to show how good we are in meeting budgeted new product launch dates.

 

Aplogies for the lonngish post, but new to Power BI and enjoying the community support immensely!

 

Thank you  

9 REPLIES 9
amitchandak
Super User
Super User

Anonymous
Not applicable

Unfortunately it doesnt seem to be working:

For example, trying to calucluate budgeted luanch date:

 

Budgeted Launch Date = FIRSTNONBLANKVALUE('Street Sales transactional Data'[Street Sales Budget Net Sales ($)], EXPRESSION? )
 
What expression should be here? it doesnt allow 'Calendar'[Date].
 
On what basis column does it seek firstnonblankvalue? It needs be according to date.
 
THanks

@Anonymous ,

FIRSTNONBLANKVALUE('Street Sales transactional Data' [Date],sum('Street Sales transactional Data'[Street Sales Budget Net Sales ($)]))
or
sumx(summarize( 'Street Sales transactional Data' , 'Street Sales transactional Data' [Month Year],"_1",
FIRSTNONBLANKVALUE('Street Sales transactional Data' [Date],sum('Street Sales transactional Data'[Street Sales Budget Net Sales ($)]))),
[_1])

 

Sales need sum/max/min.

Date is where need first.

If the month is not used in visual, use summarize and values to get first date of month

Anonymous
Not applicable

Unfortuantely, this does not work. I require the output of the dax to be the date of the month when the first sales are recorded (before which sales where zero).

Hi @Anonymous 

Could you share some data example or screenshots which shows what you have and you expect?

 

Best Regards

Maggie

Hi @Anonymous ,

 

Use FIRSTNONBLANK instead of using FIRSTNONBLANKVALUE. It would give the date for which first sales is recorded (non zero).

Budgeted Launch Date = FIRSTNONBLANK('Street Sales transactional Data'[Date], SUM'(Street Sales transactional Data'[Street Sales Budget Net Sales ($)] ) )

 

Hope this helps!

 

Thanks,

Nikita Dalela | BI Consultant

Nice Software Solutions

Hi @Anonymous ,

 

If there is no expression to be calculated, the expression can be put as 1.

Budgeted Launch Date = FIRSTNONBLANKVALUE('Street Sales transactional Data'[Street Sales Budget Net Sales ($)], 1 )

 

Please let me know if I am missing something here?

 

 

Thanks,

Nikita

Anonymous
Not applicable

Unfortuantely, this does not work. I require the output of the dax to be the date of the month when the first sales are recorded (before which sales where zero).

Anonymous
Not applicable

Apologies. Small typo in above. For point 1 above (1 - Budgeted sales by Month last 2 years.), I meant to say Budgeted sales by month last 12 months and next 12 months are available.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.