Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Only displaying 0 when between other Data

Goodday,

 

I have a problem and hope you guys can help. I have the following graph, where i replaced blank values with 0:

My goal is to make it like this

 

 

This Graph Follows a slicer that is on the page.

To make the graph more concise i am trying to keep the values before and after any data Blank, and between data i want it to display 0. As if this graph would start at 2021-12 and end at 2022-7, but dynamicly based on the slicer.

 

Based on the following link i made a measure to count it;

https://community.powerbi.com/t5/Desktop/Graph-Values-and-Zero-Values-Only-Within-Date-Range/m-p/576603

 

Count Lines =
VAR CountLines = COUNTA('Table'[UniqueKey])
VAR MaxDate = MAX('Table'[DateProduced])
VAR MinDate = MIN('Table'[DateProduced])
VAR ExistsLaterDate = NOT (ISEMPTY (FILTER (ALLSELECTED ('Table'), 'Table'[DateProduced]>MaxDate)))
VAR ExistsEarlierDate = NOT (ISEMPTY (FILTER (ALLSELECTED ('Table'), 'Table'[DateProduced]<MinDate)))

Return
IF (NOT (ISBLANK (CountLines)), CountLines, IF (NOT (OR (ExistsLaterDate, ExistsEarlierDate)), 0))
 
Sadly enough this only returns the rows with Data, and not anything else. 

I have been fiddeling with the NOTS, <> and all other things to get it to work, but i am just not able. Do you guys have any idea how to solve this? 

 

 

 

 

 

  • daXtreme's avatar
    daXtreme
    4 years ago

    Dates is a dim that stores all dates from the 2000 year.

    Graph Value = 
var CurrentDate = SELECTEDVALUE( Dates[Date] )
var MinDateWithNonZeroData =
    CALCULATE(
        MINX(
            FILTER(
                Graph,
                Graph[Value] > 0
            ),
            Graph[Date]
        ),
        ALLSELECTED( Dates )
    )
var MaxDateWithNonZeroData =
    CALCULATE(
        MAXX(
            FILTER(
                Graph,
                Graph[Value] > 0
            ),
            Graph[Date]
        ),
        ALLSELECTED( Dates )
    )
var GraphValue = 
    if( MinDateWithNonZeroData <= CurrentDate 
            && CurrentDate <= MaxDateWithNonZeroData
            && not ISBLANK( MinDateWithNonZeroData ),
        CALCULATE(
            SUM( Graph[Value] ) + 0,
            TREATAS(
                { CurrentDate },
                Graph[Date]
            )
        )
    )
return
    GraphValue

2 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Dates is a dim that stores all dates from the 2000 year.

    Graph Value = 
var CurrentDate = SELECTEDVALUE( Dates[Date] )
var MinDateWithNonZeroData =
    CALCULATE(
        MINX(
            FILTER(
                Graph,
                Graph[Value] > 0
            ),
            Graph[Date]
        ),
        ALLSELECTED( Dates )
    )
var MaxDateWithNonZeroData =
    CALCULATE(
        MAXX(
            FILTER(
                Graph,
                Graph[Value] > 0
            ),
            Graph[Date]
        ),
        ALLSELECTED( Dates )
    )
var GraphValue = 
    if( MinDateWithNonZeroData <= CurrentDate 
            && CurrentDate <= MaxDateWithNonZeroData
            && not ISBLANK( MinDateWithNonZeroData ),
        CALCULATE(
            SUM( Graph[Value] ) + 0,
            TREATAS(
                { CurrentDate },
                Graph[Date]
            )
        )
    )
return
    GraphValue
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you very much! It works great! I even got it working with just Year-Month Values