Graph Values and Zero-Values Only Within Date Range

[Evogelpohl]

I have a Sales table with values at an hourly grain, even-hour (E.g. 1/1/2018 1:00 PM).  I have a Calendar table with the same hourly grain.  Joined.  Works fine.

I want to chart hourly SALES and show every single hour, even if sales were zero for that period.  

I've tried setting the line chart to "show data with no values".  Doesn't work.  

I've tried several variations of this DAX expression - and every time it graphs the entire date range for the calendar table (which expands from 2016 to 2020 <- bad graph as i only have data for a few months this year.

Question:  How do I force the calendar-houly table to *only return (graph)* values within the range of my real sales data (bascially, what I would expect datesbetween to do, but doesn't) - without setting filters in the pane.

SumSalesEveryHour = 
    VAR SalesGraph = SUM('TABLE_A'[SALES])+0 //Add Zero to create a "0" value for every hourly event in the calendar-hourly table for which there's null data.
    VAR StartDate = MIN('TABLE_A'[SalesDateHr]) //Graph starts here
    VAR EndDate = MAX('TABLE_A'[SalesDateHr])   //Graph ends here
    RETURN
        CALCULATE(SalesGraph, FILTER('Calendar - Hourly','Calendar - Hourly'[DateTime] >= StartDate && 'Calendar - Hourly'[DateTime] <= EndDate))

[Anonymous · Accepted solution]

@@Evogelpoh 

The “+0” will make your measure always return a value. This was you’ll never get blanks. What I think you want is to maintain a blank value if -there aren’t any sales before the last date in the period you’re analyzing, nor sales after.

With a setup like: 

If you create this measure:

Sale Amount =
VAR SaleAmountInPeriod =
    SUM ( Sales[Sale] )
VAR LastDateInPeriod =
    MAX ( 'Calendar'[DateTime] )
VAR ExistLaterSales =
    NOT ( ISEMPTY ( FILTER ( ALL ( Sales ); Sales[DateTime] > LastDateInPeriod ) ) )
VAR ExistEarlierSales =
    NOT ( ISEMPTY ( FILTER ( ALL ( Sales ); Sales[DateTime] < LastDateInPeriod ) ) )
RETURN
    IF (
        NOT ( ISBLANK ( SaleAmountInPeriod ) );
        SaleAmountInPeriod;
        IF ( AND ( ExistLaterSales; ExistEarlierSales ); 0 )
    )

And, if you have Sales entries like:

Sale table with hourly records
With out any filtering, you'll get this when you create the graph:

Pointing at First recordPointing at last record

If you than apply a filter to only show November, you get this:

[Anonymous]

Great Solution, It is working perfectly. Thank you