Forum Discussion
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".
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:
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).
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])-1And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
4 Replies
- MFelix
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.
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 🙂
v-kelly-msftCommunity 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])-1And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
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!
