Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Show/hide items with no data basics

Hi all,

I would appreciate a little help, this may be very simple  ....

 

I am creating a simple table : 

 

As you can see no sales in April.

 

I am creating a simple chart : 

The chart has no column for April, which makes sense, as I have no "0" sales value for April. However I'd like to show it as a 0 sales, so I am using the visual "show items with no data" option, that results into this : 

 

Now I have my empty April, but also have Q3/Q4 - Why is powerbi extending the graph beyond my last date ?

 

I am speculating that because I am using the default "auto date/time" option, Powerbi creates the date table for the 2019 year, and uses this as a reference to create the graph and determining the max date for x axis. 

 

I can of course filter on the dates to show only the relevant Period (Jan to Jun),  or create my own date table, however I would have expected it to be straightforward.  Did I miss something  ?

 

How are you dealing with this simple use case ?

Thank you.

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    To create a measure as below.

     

    Measure = 
VAR maxd =
    MAXX ( ALL ( 'Table' ), 'Table'[date] )
RETURN
    IF (
        MIN ( 'DATE'[Date] ) > maxd,
        BLANK (),
        IF ( SUM ( 'Table'[sales] ) = BLANK (), 0, SUM ( 'Table'[sales] ) )
    )

     

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    To create a measure as below.

     

    Measure = 
VAR maxd =
    MAXX ( ALL ( 'Table' ), 'Table'[date] )
RETURN
    IF (
        MIN ( 'DATE'[Date] ) > maxd,
        BLANK (),
        IF ( SUM ( 'Table'[sales] ) = BLANK (), 0, SUM ( 'Table'[sales] ) )
    )