Forum Discussion

TimNYC's avatar
TimNYC
Icon for Helper I rankHelper I
6 years ago

Removed Unwanted Dates from X-Axis

I'm trying to build a report that will show all sales from the previous two weeks through the future two weeks. So the dates range I would like to see on the X-Axis is 6/28 - 8/1 (since today is 7/14...
Need Help
TimNYC's avatar
TimNYC
Icon for Helper I rankHelper I
6 years ago

Thank you very much for the example. I tried using this technique with my data but it still hasn't helped with my X-Axis. The table "datesTwoWeeks" contains only dates from Jun 28 - Aug 2, and I'm using that table for the X-Axis, but my X-Axis is still showing the whole year. What could I be doing wrong? 

 

 

The statement for my measure is: 

TTM_Sales =
VAR FDate = MIN(datesTwoWeeks[Date])
VAR LDate = MAX(datesTwoWeeks[Date])
VAR Res =
CALCULATE (
IF(CALCULATE(SUM('append'[sourceQuantity]))=BLANK(),0,CALCULATE(SUM('append'[sourceQuantity])))
,FILTER (
'append'
,'append'[Performance Category] = "Sold" && 'append'[Date] >= FDate && 'append'[Date] <= LDate
)
)
RETURN Res

 

 

TimNYC's avatar
TimNYC
Icon for Helper I rankHelper I
6 years ago

v-deddai1-msft Ok, I have part way figured it out. When I remove the part of my measure that replaces the number of sales on days that are blank with 0, then my X-Axis properly filters to only the dates I'm looking for. However, now July 5 is missing from the X-Axis. I need July 5 to be present on the graph, and show up as zero sales on that day. Is there another technnique I should be using to replace days that are missing from my data with zero sales?

 

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi TimNYC ,

     

    Would you please use show item with no data:

     

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

     

    Best Regards,

    Dedmon Dai

  • TimNYC's avatar
    TimNYC
    Icon for Helper I rankHelper I
    6 years ago

    Thank you for continuing to assist v-deddai1-msft. "Show items with no data" nullifies the measure's filter and causes all dates, from 1/1/2017 - 12/31/2020, to be shown. It also causes July 5 to show up as blank on the table and the cart, instead of as zero. I need all dates that do no contain sales to show up as zero, and for the X-axis to show only the dates 6/28 - 8/2.