Waterfall chart for opened, closed, last period breakdown

[paweldm]

Hi there,

I am trying to apply the idea of the waterfall chart developed by  @SqlJason to present breakdown of active pharmacies per selected year . It is available here: http://sqljason.com/2015/08/using-dax-to-make-your-power-bi-dataviz.html

I need the following measures:

# of opened pharmacies (selected year)

# of closed pharmacies (selected year)

# of active pharmacies (year before the selected one)

I only have the measure to calculate # of active pharmacies and I belive that the function SAMEPERIODLASTYEAR could be helpful to get the 3 rd measure. 

Pharmacy Active = CALCULATE(
	COUNT('pharmacy'[pharmaid]);
		FILTER(
				'pharmacy';
				'pharmacy'[opendate] <= LASTDATE('Date'[Date]) &&
				('pharmacy'[closeDate] >= FIRSTDATE('Date'[Date]) || ISBLANK('pharmacy'[closeDate]))
			)
)

Can anyone help me to deal with my case?

Pawel

[dedelman_clng]

You are correct.  SAMEPRIODLASTYEAR can be used in this situation.  Removing the explicit FILTER expression from CALCULATE should make it easier to work with.

Pharmacy Active =
CALCULATE (
    COUNT ( 'pharmacy'[pharmaid] ) ;
    'pharmacy'[opendate] <= LASTDATE ( 'Date'[Date] ) ;
    OR (
        'pharmacy'[closeDate] >= FIRSTDATE ( 'Date'[Date] ) ;
        ISBLANK ( 'pharmacy'[closeDate] )
    ) ;
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Hope this helps,

David

[paweldm]

@dedelman_clng

I did "copy & paste" your measure and I got the error: 

"A function 'LASTDATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

Can you fix that ?

Can you also help me with 2 other measures to calculate # of opened pharmacies and # of closed pharmacies for a selected year?

Pawel

[dedelman_clng]

My apologies - I didn't test the syntax of the code before posting.  It seems we can't clean up the FILTER like I thought:

Pharmacy Active LY =
CALCULATE (
    COUNT ( 'pharmacy'[pharmaid] ) ;
    FILTER('pharmacy';
                 'pharmacy'[opendate] <= LASTDATE ( 'Date'[Date] ) &&
                 OR (
                     'pharmacy'[closeDate] >= FIRSTDATE ( 'Date'[Date] ) ;
                     ISBLANK ( 'pharmacy'[closeDate] )
                 )
    ) ;
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Your initial measure for active pharmacies should work as is.  For closed pharmacies you want to change the logic around 'pharmacy'[closeDate] to be between FIRSTDATE and LASTDATE so try:

Pharmacy Closed =
CALCULATE (
    COUNT ( 'pharmacy'[pharmaid] ) ;
    FILTER('pharmacy';
                 'pharmacy'[opendate] <= LASTDATE ( 'Date'[Date] ) &&
                 'pharmacy'[closeDate] >= FIRSTDATE ( 'Date'[Date] ) && 
                 'pharmacy'[closeDate] <= LASTDATE ('Date'[Date])
                 )
    ) 

Hope this helps.

David

[paweldm]

@dedelman_clng

I executed your last measure of 'Pharmacy Active LY', but it calculates the # of active pharmacies for the selected year instead of the year before. In other words, the measure gives the same result as my initial measure '# of active pharmacies for the selected year'.

However, I have slightly modified your measure. I added one more calculate function to limit the results to the 'SAMEPERIODLASTYEAR' time frame. 

Pharmacy Active LY =
CALCULATE (
    CALCULATE (
        COUNT ( 'pharmacy'[pharmaid] );
        FILTER (
            'pharmacy';
            'pharmacy'[opendate] <= LASTDATE ( 'Date'[Date] )
                && OR (
                    'pharmacy'[closeDate] >= FIRSTDATE ( 'Date'[Date] );
                    ISBLANK ( 'pharmacy'[closeDate] )
                )
        )
    );
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

Although, I got the correct number, it only happens when I select 2 different consecutive years. And that is not what I need since I need to select one year on the slicer and get the value of active pharmacies one year before the selected year. 

I hope you are  still with me to solve it. 

Pawel

[dedelman_clng]

Hi @paweldm

I had a feeling this might've needed a second nested CALCULATE, so I'm glad you were able to find that.

Can you share an version of your PBIX with any confidential data scrubbed out?  I've run Year-over-Year calculations with SAMEPERIODLASTYEAR in several reports and haven't had issues up to this time, but my data models may not have been as complex as yours.

[paweldm]

Hi @dedelman_clng

I am attaching simplified model. The model includes just 2 tables required to perform # of active pharmacies and of active pharmacies LY calculations . 

My comments:

1. If I don't select  any years on the year slicer, the 'Pharmacy Active LY' indicates the number (20990) I don't know where it comes from.
2. If I select single year on the year slicer, the 'Pharmacy Active LY'  calculates 'blank' value
3. If I select two consecutive years, the measure works correct.
4. If I select two non-consecutive years , the measure doesn't work.

https://drive.google.com/file/d/0B_3HyUcBG_MdTW5LOTJmSnRMZm8/view?usp=sharing

Pawel

[dedelman_clng]

Hi @paweldm

Sorry for the delay in getting back to you - had to find a creative way of downloading the pbix as my company firewall blocks google drive 😐

Anyway, first thing I notice is that there is no relationship between the pharmacy table and the Date table.  You need to create one in the modeling view - preferably from Opendate to Date.  You can also create one between Closedate and date, but that one will remain inactive for now.

Now Pharmacy Active LV works for a single selection, and it gives a value for a selection of two consecutive years.  However it breaks when two or more non-consecutive years are chosen (this makes sense as there is no "last year" in the selection once non-consecutive years are chosen).

I came up with another measure using PARALLELPERIOD that works with all kinds of selections, but it got me wondering what you are trying to see in Pharmacy Active LV when more than one year is chosen (consecutive or non-consecutive). 

If you were to select 2007, the graph would show 984, and the LY value would show 851 (the value for 2006). But if you select 2006 and 2007, what are you expecting to see in the LY value?  The value of 2005?  The combined value of 2004 and 2005? The combined value of 2005 and 2006 (incidentally, this last one is the value that LY shows in its current form).

Then for non-consecutive years, what are you expecting to see in the LV measure?

I think we're almost there and you'll have your nice waterfall before you know it 🙂

[paweldm]

Hi @dedelman_clng

1. There is no relationship between the pharmacy table and date table in order to calculate the # of active pharmacies by year. I have already attached the stacked column chart to the model. If you connect both tables, the measure will not work correct.
   • Do you know how to deactivate the tables connection? The opposite commend to 'USERELATIONSHIP' would be helpful in this case.
2.  I think the most intuitive for report user would be to apply the following logic:
   • select one year in slicer =>(eg. if 2017 year is selected, the measure 'Pharmacy Active Last Year" calculates all active pharmacies in 2016 and # of opened  and # of closed pharmacies are calculated for 2017 by two other measures
   • select combination of consecutive years in the year slicer =>(eg. 2016 and 2017 years are selected, the measure 'Pharmacy Active Last Year" calculates all active pharmacies in 2015  and # of opened and # of closed pharmacies are calculated for 2016 and 2017 together as a sum by two other measures
   • select combination of non-consecutive years => (eg. 2015 and 2017 years are selected, the measure 'Pharmacy Active Last Year" calculates all active pharmacies in 2014 and # of opened and # of closed pharmacies are calculated for 2015 and 2017 together as a sum by two other measures

Thank you for your strong drive to solve my case!

Pawel

[dedelman_clng]

Hi @paweldm -

Give this a shot.  It calculates a single value based on the years selected, so putting it with other measures on a graph is kind of meaningless.  But you may be able to use it to then calculate some kind of variance and plot that on your waterfall.

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

Let me know if this helps

David

[paweldm]

Hi @dedelman_clng

Your new measure 'PALY' works excellent. Thank you so much. I am impressed by your DAX skills. Power On!

However, I believe I must have made a mistake with the 'Pharmacy Opened' since I can't get the correct value for the equation: Pharmacy Active Last Year + Pharmacy Opened - Pharmacy Closed.

Pharmacy Opened = 
CALCULATE (
    COUNT ( 'pharmacy'[pharmaid] ) ;
    FILTER('pharmacy';		
                 'pharmacy'[openDate] >= FIRSTDATE ( 'Date'[Date] ) && 
                 'pharmacy'[openDate] <= LASTDATE ('Date'[Date])
                 )
)

 https://drive.google.com/file/d/0B_3HyUcBG_MdTW5LOTJmSnRMZm8/view?usp=sharing

Can you please have a last look into that issue?

Pawel

[dedelman_clng]

Hi @paweldm

I've done some more looking at this, and I think the problem might be in "Pharmacy Active".  Pivoting your data in Excel, Pharmacy Opened and Pharmacy Closed are both correct for 2016.  But if Pharmacy Active is incorrect, then Pharmacy Active LY is also incorrect (since it is doing the same calculation, just over a different timespan).  I was hoping it was something having to do with the non-existent relationship to the date table, but it isn't.

According to my calculations in Excel the number of Pharmacies active at the end of 2016 was 15160.  We now need to work on the Pharmacy Active measure to get that to match.

I will update you when I have more.

David

[dedelman_clng · Accepted solution]

OK, here should be the final answers (I am putting all of the DAX in so this post can be marked as the solution and have all of the answers):

Pharmacies Opened in a given year

Pharmacy Opened = 
CALCULATE (
    COUNT ( 'pharmacy'[pharmaid] ) ,
    FILTER('pharmacy',
                 'pharmacy'[openDate] >= FIRSTDATE ( 'Date'[Date] ) && 
                 'pharmacy'[openDate] <= LASTDATE ('Date'[Date])
	      )
)

Pharmacy Closed in a given year

Pharmacy Closed = 
CALCULATE (
    COUNT ( 'pharmacy'[pharmaid] ) ,
    FILTER('pharmacy',
                 'pharmacy'[opendate] <= LASTDATE ( 'Date'[Date] ) &&
                 'pharmacy'[closeDate] >= FIRSTDATE ( 'Date'[Date] ) && 
                 'pharmacy'[closeDate] <= LASTDATE ('Date'[Date])
                 )
    ) 

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 )

Let me know how the rest of the report goes.

David

[paweldm]

@dedelman_clng

Hi David,

You did a fantastic DAX pattern. Your pattern has a very clear flow and perfectly solves my case to build the required pharmacy report. Once again many thanks. I believe your pattern is a very strong contribution to the Power BI users community since calculations of events in progress are very common, yet they are very complex. 

I am attaching the link to my report including all your measures. 

Pharmacy report

Power ON!

Pawel