Forum Discussion
Apply Certain Date Range using ISBLANK
Hi Community,
I have a measure to calculate total sales:
M= SUM(Sales[sales])
Now I have put Date Range to show next 6 Months including current Month Feb 2021.
N= CALCULATE(M, DATESINPERIOD( Date, NOW(), 6 , MONTH)))
This is result :
May and July is blank hence doesnt appear , to show them on the graph;
Y= IF(ISBLANK(N), 0 , N)
This pulls up entire date data and shows zero for entire date where as I needed for next 6 months only.
schoden , Try like
0 between range Measure = var _1= CALCULATE(SUM(Sales[sales]), DATESINPERIOD( Date, NOW(), 6 , MONTH)) +0 var _min = calculate(minx('Calendar', 'Calendar'[Date]) ,DATESINPERIOD( 'Calendar'[, NOW(), 6 , MONTH)) var _max = calculate(maxx('Calendar', 'Calendar'[Date]) ,DATESINPERIOD( 'Calendar'[, NOW(), 6 , MONTH)) return CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))
10 Replies
schoden , Create a measure like below example
0 between range
Measure = var _1= SUM(sales[sales]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))hi amitchandak the measure still doesnt show zero for the blank months.
schoden , here min-max, need to be from six-month range.
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
Hi Ashish_Mathur Thanks for the share . I am facing problem to show zero value for all blank months in the specified date range.
