Display remaining dates in the month with a zero value

[Arturo24]

Newbie here

The fact table has sales for 1-Jan through 15-Jul

The date table has date entries for the entire year

How do you return sales for each day from 1-Jan through 31-Jul where each day from 16-Jul through 31-Jul show a zero?  Don't want to go beyond 31-Jul.

Thank you

[v-xiaoyan-msft]

Hi @Arturo24 ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Arturo24]

I meant your solution looks much cleaner 😁

[amitchandak]

@Arturo24 , Hope you have selected Jul 2021 in Silcer

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +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))

[Arturo24]

Now I have an additional question on returning all dates:  The goal was to return ALL dates from first sales to the end of the current month, which we said was 31-Jul. 

Situation:
Salesperson A has sales in July , Salesperson B has sales in June.  If you filter by salesperson, for person A we see dates all the way through 31-Jul.  For person  we see sales up through 30-Jun. 

Question:

How can I keep the filter on the individual sales person but still return all dates until the end of the month, 31-Jul?  For whichever sales person I select how do I return the exact same (entire) date range?

I tried All() but it doesn't work.  

Thank you

[Ashish_Mathur]

Hi,

To your Table visual, drag Dates from the Calendar Table and write this measure

Total = sum(Data[Sales])

Hope this helps.

[Arturo24]

I did something like below.  I used the IF statements in case there were dates between the two ranges where there were no sales.  Would also like to see those dates.  I think your variable _1 does that by adding zero to a blank entry.  Looks much cleaner.  Thanks. 

0 between Range Measure =
VAR CurrentDate = max( dDate[date] )
VAR EOM = EOMONTH(today (),0 )
VAR DateRange = CurrentDate >= min('Sales'[SaleDate]) && CurrentDate <= EOM
VAR Sales = if ( DateRange,
if ( isblank ( SUM ( Opportunity[Opportunity count] ) ) , 0 ,
                    SUM ( Opportunity[Opportunity count] ) )
)
Return
Sales