Filter 2 range of dates from the same table

[getitout]

Hi,

I am very new to Power BI, need your advice.

I would like to compare one column (e.g. Performance KPI) but from different date range e.g. Pre and Post 

Pre selection dates -Filter (slicer)

Post selection dates - Filter (slicer)

Performance KPI = AVERAGE(PerformanceKPI)

example of data

Date              PerformanceKPI

1/1/2020       10

1/2/2020       9

1/3/2020       8

..

5/1/2020      10

5/2/2020      10

I try to duplicate date to post date, and use slicer but it did not work since pre date and post date is the same date.

Try to use calculate but cannot make it work, please advise.

Thank you very much

[v-lili6-msft · Accepted solution]

hi   @getitout 

You could refer to this blog:

Filtering and comparing different time periods with Power BI

Regards,

Lin

[amitchandak]

Can you check, if this can help :https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[getitout]

Thank you very much. Will try and let you know.

[Ashish_Mathur]

Hi,

Please be very clear in telling us what exactly would you be selecting in the Date slicers - will it be a single date or a range of dates?  Why do you need two slicers - why not just one?  Take a clear example and show the expected result.

[getitout]

There will be 2 slices since we want to select pre dates (range) and post date range, which is not overlapped.

Thanks for the comment, this is my first post. Will try to write clearer.

[Ashish_Mathur]

Hi,

I still do not understand.  I am willing to help if you take a clear example and show the exact expected result.

[getitout]

Thanks for all the suggestion.

I've got the solution from the community to use cross join

Parameters =

var periods = VALUES(data[startday])

return CROSSJOIN(

SELECTCOLUMNS(periods,"Pre", [startday]),

SELECTCOLUMNS(periods,"Post", [startday])

)

Average KPI=

var period1 = CALCULATE(

AVERAGE(data[KPI]),

TREATAS(VALUES(Parameters[Pre]),pm[starttime])

)

var period2 = CALCULATE(

AVERAGE(data[KPI]),

TREATAS(VALUES(Parameters[Post]),data[startday])

)

return IF(period2>period1, "PASS", "FAIL")

I will your suggested method as well.

Thank you very much.