Forum Discussion

u92690's avatar
u92690
Frequent Visitor
5 years ago
Solved

DATESINPERIOD when the base date does not exist

Hey guys,

 

I have a table with sales by date, and I have created a measure to sum the sales made in the last 7 days. The thing is that, when it comes a day in which haven't sold anything (and therefore the are no rows that contain that date), the measure is not showing up the value for that date. 

 

So far I have been fixing it by appending another table "blank" with all dates and 0 sales, which works fine, but it doesnt seem to be very professional, so if anyone could give me a hand it would be cool. 

 

Thanks!

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    u92690 , +0 or handle blank are correct option.

     

    or a formula like those, when you have date range

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

     

    or this option -> show item with no data

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    u92690 , +0 or handle blank are correct option.

     

    or a formula like those, when you have date range

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

     

    or this option -> show item with no data