Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
John2029
Frequent Visitor

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

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".

date.png

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

John2029_1-1601632771132.png

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).

 

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

Screenshot 2020-10-05 172529.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
Community Support

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:

Screenshot 2020-10-05 172529.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

Thanks a lot for your reply 

MFelix
Super User
Super User

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.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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   

filternoconnect.png

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

Valuestartend.png

 

But the increase shows -100%.

 

increasenogood.png

 

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 🙂

 

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.