Forum Discussion

Evogelpohl's avatar
Evogelpohl
Helper V
7 years ago
Solved

Graph Values and Zero-Values Only Within Date Range

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...
  • Anonymous's avatar
    Anonymous
    7 years ago

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