Waterfall chart to visualize number of open and closed items in each calendar week

[sak]

Hello,

I am trying to build a waterfall chart with calendar wek on X axis and number of items on Y axis. I wan to visualize number of open iems and closed items in each week. Sample data:

Expected result:

[sak]

This is how I want the waterfall chart to look like 

[v-evelk]

Hello,

You can use d3 and combine waterfall chart with the line, using different layers.

For waterfall I found this example that looks exactly what you look for.

Kind Regards,

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

[sak]

Hello,

Thanks for sharing. But it is difficult to interpret and understand the code. Do you suggest any other simpler way to understand it? 

[Anonymous]

To calculate the number of currently active pharmacies in a given year (based on the slicer), we are going to create two measures that will denote whether or not the pharmacy was opened prior or during to the selected year(s) (by giving a value of 1) and/or closed prior to or during the selected years (giving a value of -1)

IsOpen =
IF (
    CALCULATE ( MINX ( pharmacy, pharmacy[openDate] ) ) <= LASTDATE ( 'Date'[Date] ),
    1,
    0
)

IsClosed =
VAR minDate =
    CALCULATE ( MINX ( pharmacy, pharmacy[closedate] ) )
RETURN
    IF (
        AND ( minDate <= LASTDATE ( 'Date'[Date] ), NOT ( ISBLANK ( minDate ) ) ),
        -1,
        0
    )

Finally, the number of current active pharmacies is simply the sum of IsOpen and IsClosed

Pharmacy Active =
CALCULATE ( SUMX ( pharmacy, [IsOpen] ) + SUMX ( pharmacy, [IsClosed] ) )

And the number of active pharmacies in the year prior to the minimum selected year

Pharmacy Active LY = 
VAR minSelYear =
    YEAR ( CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date'[Year] ) ) )
VAR PAMinSel =
    CALCULATE ( [Pharmacy Active], 'Date'[Year] = minSelYear - 1 )
RETURN
    IF ( ISBLANK ( PAMinSel ), 0, PAMinSel )