Forum Discussion
Helper IReferencing Measures within Calculate
Hi, ihave created the follwoing code below to show zeros on a graph where the DATA doesnt exist, see below:
Concessions Raised = SUMX(FILTER('Date Table','Date Table'[Date] <= TODAY()),CALCULATE(COUNT('Sub_Concession'[Date_Raised])+0))
It works as intended - i get a graph with 0 if the data doesnt exist and it filters between my dates.
However - i want to do this with a measure, instead of a column reference. the current code is below - how can i make this do the same as the above?
DPPM Number = SUM('Sub_Cust Reject'[Qty]) / SUM('Sub_Release Notes'[Qty]) * 1000000 + 0
Thank you
Hi amitchandak - I have figured the issue and the below does what i want to do
DPPM Number = AVERAGEX(FILTER('Date Table','Date Table'[Date] <= TODAY()) , SUM('Sub_Cust Reject'[Qty]) / SUM('Sub_Release Notes'[Qty]) * 1000000 + 0)Thanks for getting me on my way 🙂
5 Replies
- amitchandak
Super User
Jackbaz99 , Th second measure is fine. DO you want to add filter to that ?
example
DPPM Number = CALCULATE(SUM('Sub_Cust Reject'[Qty]) / SUM('Sub_Release Notes'[Qty]),FILTER('Date Table','Date Table'[Date] <= TODAY())) * 1000000 + 0
Jackbaz99amitchandak Hi, ive tried this but the dates still don't filter to the specific dates for some reason - i want to show ZEROS when the data is blank as the first measure does.
However this one still shows all of them. not filtered.- Jackbaz99
amitchandak i am wondering if i need to use firstdateinperiod & lastdateinperiod
- amitchandak
Jackbaz99 , you means
firstnonblankvalues and lastnonblankvalues
https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak
- Jackbaz99
Hi amitchandak - I have figured the issue and the below does what i want to do
DPPM Number = AVERAGEX(FILTER('Date Table','Date Table'[Date] <= TODAY()) , SUM('Sub_Cust Reject'[Qty]) / SUM('Sub_Release Notes'[Qty]) * 1000000 + 0)Thanks for getting me on my way 🙂
