Forum Discussion

John2029's avatar
John2029
Frequent Visitor
5 years ago
Solved

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...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

MFelix's avatar
MFelix
Super User
5 years ago

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's avatar
John2029
Frequent Visitor
5 years ago

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 🙂