Apply Certain Date Range using ISBLANK

[schoden]

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.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[schoden]

Hi @Ashish_Mathur  Thanks for the share .  I am facing problem to show zero value for all blank months in the specified date range. 

[amitchandak]

@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))

[schoden]

hi @amitchandak  the measure still doesnt show zero for the blank months.

[amitchandak]

@schoden , here min-max, need to be from six-month range.

[schoden]

Yes @amitchandak  I have put flag check for next 6 months.  still doesnt show zero

[amitchandak]

@schoden , I tried on my file. Please check the file attached after the signature. Check Page 13

[schoden]

@amitchandak  Thanks for the share.

However I need for next 6 months including current month from 01/02/2021 that is current month start date. 

This filter starts from 8/02/2021 as its todays date. 

I have used datesinperiod with specified start and end date 

[amitchandak · Accepted solution]

@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))

[schoden]

Thank you so much