Display a calculated value based on start date and end date filters

[John2029]

Hello Power BI community,

How can I display the % increase based on 2 values which are themselves based on the selection of 2 dates (start date and end date).

For example: I select on a "start date" filter "July 2019" and on an "end date" filter "February 2020".

The report would then look at the value corresponfing to July 2019 and the value corresponding to February 2020.

Let's say the value is 100 for July 2019

And 160 for February 2020

The result would display 60%*.      *Calculation would be ((160/100)-1)*100 = 

My data is composed of a :

Value column- display a list of numerical values

Date column  - display a list of dates (day, month, year)

What I did:

1. open query editor> create two additional columns (start date and end date) > close and apply

2. Create 2 measures to display my values based on the "start date" filter and "end date" columns i have created above:

value start date = CALCULATE([value], 'MyTable'[Start date])

value end date = CALCULATE([value], 'MyTable'[End date])

3. Create a final measure that calculates my % increase:

%increase = DIVIDE([value start date], [value end date])-1

4. I create 2 date filters : "start date" with MyTable'[Start date] and "end date" with MyTable'[End date]

But somehow, I cannot manage to connect my measures (value start date and value end date) to the visual filters start date and end date I created in step 4 above.

When I display my "value start date" measure and "my value end date" measure, only the "value start date" measure shows a value which is not based on the specific filter 'MyTable'[Start date]) that I assigned in my measure.

How can I connect my 2 measures "value first date" and "value end date" to their respective filter "start date" and "end date"?

It's my first post, I hope I am not confusing you too much. Any help would be very much appreciated.

Thanks a lot (haha I'm sure you guessed I m a super beginner).

[v-kelly-msft · Accepted solution]

Hi  @John2029 ,

You need to create 2 slicer tables as below:

Start date = VALUES('Table'[Start date])

End date = VALUES('Table'[End date])

Then create 3 measures as below:

startdate value = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[Start date]=SELECTEDVALUE('Start date'[Start date])))

Enddate value = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),'Table'[End date]=SELECTEDVALUE('End date'[End date])))

%increase = DIVIDE('Table'[startdate value], 'Table'[Enddate value])-1

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[John2029]

Thanks a lot for your reply v-kelly-msft ! 

And for taking the time to build a report as well.

Very helpful thank you again very much!

[MFelix]

Hi @John2029 ,

You need to create the Start and End filter based on disconnected tables and then make your calculations based on that.

value start date = CALCULATE([value], Filter('MyTable','MyTable'[Start date] = MAX(StartDate[Date]))


value end date = CALCULATE([value], Filter('MyTable','MyTable'[End date] = MAX(EndDate[Date]))

Now you can divide this two values. 

The trick is having the disconnected tables for the filtering of the data.

[John2029]

Hi MFelix,

Ah, thank you for your reply! 

1. I changed the "value end date" and "value start date" measures to filter on max date as you advised

value end date = CALCULATE([Value measure],FILTER(Sheet1,Sheet1[End date]= MAX(Sheet1[End date])))

value start date = CALCULATE([Value measure],FILTER(Sheet1,Sheet1[Start date]= MAX(Sheet1[Start date])))

2. I disconnected the slicer "Start date" and "End date" one from each other using the "Edit interaction" feature in the Format menu to be able to select a different start date and a different end date   

3. I created a measure to show the increase in % : 

% Increase = DIVIDE([value end date], [value start date])-1

The start value and end value show the correct value based on date selection

But the increase shows -100%.

I am trying to make it do 160/100-1 to show 60%.

Do you have any idea what I am doing wrong?

Thank you again very much for your initial answer on the filter 🙂