cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aemb Frequent Visitor
Frequent Visitor

Blank() value for dates from end of current month

Hi all

 

I am struggling with a measure... hope you can help! 

 

I have a database with contracts, and one with sales orders (by shipdate). 

I created a measure which flags whether sales orders are higher, lower, or OK compared to the contract that month. The measure gives Blank() values for either no contract/no sales order combination. But I would also like it to give Blank() for any date beyond the end of the current month.

This last part is where I cannot manage to get it to work. The underlined part in the measure throws an error: a single value for column timestamp_dt in table date dimension cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation.

 

any ideas? 

 

 

Contract Application =
IF(
      AND(ISBLANK([Quantity Shipped by Actual Ship Date]),ISBLANK([ContractQty by Del/Ship From])),
          BLANK(),
         IF( (([ContractQty by Del/Ship From] - [Quantity Shipped by Actual Ship Date])/[ContractQty by Del/Ship From])>0.05,
      IF((Year('date_dimension'[timestamp_dt])*100+Month('date_dimension'[timestamp_dt]))>(YEAR(Today())*100+ MONTH(Today())),Blank(),"Lower"),
          IF( (([ContractQty by Del/Ship From] - [Quantity Shipped by Actual Ship Date])/[ContractQty by Del/Ship From])<-0.05, "Higher","OK"))
)
'date_dimension'[timestamp_dt] --> main date column (eg 09-07-17 ) 
 
Expected result:
Quantity Shipped by Actual Ship DateContractQty by Del/Ship Fromyearmonthnumber (Aggragated)Contract Application MTD
23940,9721043.14201810Higher
24804,0223642.94201811OK
17454,8620688.44201812Lower
24007,7023047.65201901OK
20452,6823990.67201902Lower
20789,2524487.06201903Lower
2852,0722460.74201904Lower
 18994.67201905 
 20451.6201906 
 19995.34201907 
 19335.41201908 
 
Any help would be greatly appreciated...!!
2 REPLIES 2
Super User
Super User

Re: Measure with blank() value for sales order dates from end of current month

@aemb please read this post to get your answer quickly https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





aemb Frequent Visitor
Frequent Visitor

Re: Blank() value for dates from end of current month

Would still love the community's input on this one! let me know in case the ask is unclear! Thanks a lot in advance:)

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)