cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Evogelpohl
Helper V
Helper V

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

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@@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: relation.JPG

 

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 recordsSale table with hourly records
With out any filtering, you'll get this when you create the graph:

 

Pointing at First recordPointing at First recordPointing at last recordPointing at last record

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@@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: relation.JPG

 

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 recordsSale table with hourly records
With out any filtering, you'll get this when you create the graph:

 

Pointing at First recordPointing at First recordPointing at last recordPointing at last record

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

Great Solution, It is working perfectly. Thank you

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.