% change of open events

[paweldm]

Hi ,

I am calculating events in progress based on the startDate and closeDate over year.

I use 2 tables.

table1 includes: pharmaId | startDate | closeDate

table2 is a date table and it includs date | year | month | etc. 

Tabel 2 is not conected with table 1. 

The measure to calculate open events works fine.

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

I need to calculate the % change of open pharmacies over time.

For example, I would like to select 2 years from the date table in the slicer: 2010 and 2015 and calculate the measure to find out the % change of open pharmacies from 2010 to 2015.

However, I would like to select dynamically other years as well and switch to another period % change calculation. 

In other words, I would like to apply the % change measure for my calculation of events-in-progress.

I have found the tutorial on dynamic % change measure, but I don't know how to modify it to my case of open events.

https://powerbi.tips/2016/06/dynamic-percent-change-using-dax/  

Can anyone help me to deal with that?

Pawel

[paweldm]

I belive I am now a bit closer to the solution.

I built the table with SUMMARIZE function:

SummarizeActive =
SUMMARIZE ( 'Date'; 'Date'[Year]; "Total Active By Year"; [Pharmacy Active] )

Next I followed the manual from https://powerbi.tips/2016/06/dynamic-percent-change-using-dax/  and got the % change with 'Year' slicer from newly created 'SummarizeActive' tabel.

However, I need to be able to add to the slicer [Year] column from the 'Date' table to dynamically change the stacked column chart with the measure of [Pharmacy Active] and in the same time dynamically calculate % change. 

[Vvelarde · Accepted solution]

@paweldm

Hi, i give you a idea:

Using a Slicer With Years (From Calendar Table).

3 Measures

PharmacyActiveFirstYearSelected =
VAR FirstyearSelected =
    FIRSTNONBLANK ( VALUES ( 'Calendar'[Year] ), 'Calendar'[Year] )
VAR LastDay =
    CALCULATE (
        LASTDATE ( 'Calendar'[Date] ),
        FILTER ( 'Calendar', 'Calendar'[Year] = FirstyearSelected )
    )
RETURN
    CALCULATE (
        COUNTROWS ( Pharmacy ),
        FILTER (
            ALL ( Pharmacy ),
            Pharmacy[Start Date] <= lastday
                && (
                    Pharmacy[Close Data] >= lastday
                        || Pharmacy[Close Data] = BLANK ()
                )
        )
    )

PharmacyActiveLastYear =
VAR LastyearSelected =
    LASTNONBLANK ( VALUES ( 'Calendar'[Year] ), 'Calendar'[Year] )
VAR LastDay =
    CALCULATE (
        LASTDATE ( 'Calendar'[Date] ),
        FILTER ( 'Calendar', 'Calendar'[Year] = LastyearSelected )
    )
RETURN
    CALCULATE (
        COUNTROWS ( Pharmacy ),
        FILTER (
            ALL ( Pharmacy ),
            Pharmacy[Start Date] <= lastday
                && (
                    Pharmacy[Close Data] >= lastday
                        || Pharmacy[Close Data] = BLANK ()
                )
        )
    )

%Change =
DIVIDE ( [PharmacyActiveLastYear], [PharmacyActiveFirstYear] ) - 1

[paweldm]

@Vvelarde

Hi Victor!

Your measures work excellent. It is the solution I needed.

Thank you so much. I am so impressed by your DAX skills. Power On!

p.s. I have only changed the "ALL" function into "ALLSELECTED" in the 13th line to use other slicers in my data model. 

Pawel