Creating 2 charts: 1 before Date X, and 1 after Date X

[Anonymous]

Hi Power BI Community,

I'm currently trying to create a side-by-side comparison between 2 charts: one that shows results before 2018 and one that shows results after 2018. I can simply hard-code this under visual filter, but for future analysis, ideally, there would be a single date parameter. 1 chart would filter for everything before the date parameter and the other would filter for everything after the date parameter. Is this currently doable in Power BI?

Thanks!

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

One sample for your reference, please check the following steps as below.

1. Create a what if parameter from 2017- 2021.

Parameter = GENERATESERIES(2017, 2021, 1)

Parameter Value = SELECTEDVALUE('Parameter'[Parameter])

2. To create two measures to work on it.

after = CALCULATE(SUM('Table'[sales]),FILTER('Table','Table'[year]>=Parameter[Parameter Value]))

before = CALCULATE(SUM('Table'[sales]),FILTER('Table','Table'[year]< Parameter[Parameter Value]))

[Anonymous]

Thank you, @v-frfei-msft ! This is exactly what I needed.

For anyone else who wants to do a before-after comparison for a certain month and year:
0. Follow @v-frfei-msft's step 1
1. Create a what-if parameter from 1-12 for month
2. Use the following measure:

CALCULATE(SUM('Table'[sales]), FILTER('Table', OR(YEAR('Table'[Date])>[@Year Value],MONTH('Table'[Date])>=[@Month Value] && YEAR('Table'[Date])=[@Year Value])))

[Anonymous]

Hi @Anonymous 

Sure is 🙂

You can create a column which puts in a 1 if the date is in, on or before a date and then use that to filter. I use a date table and it populates if that day was 1 week ago. Then you can select your visual with a filter so it shows 1 week ago = 1.

1 week ago = if( 

LOOKUPVALUE('Date Table'[Running Week],'Date Table'[Date],'Your Table'[Days]) = 

(LOOKUPVALUE('Date Table'[Running Week],'Date Table'[Date],today())-1) 

, 1,0)

and this is my date table.

[Anonymous]

Hi @Anonymous 

Thank you for the quick reply! Your post has given me some ideas moving forward. But my main question is, let's say you want to now show data for things that were 2 weeks ago, or 3 weeks ago. You would have to go back to the measure and manually change the formula for it. Is there a way to use a parameter to dynamically change the formula? 

Thanks!

[Anonymous]

Hi,

Do you have a set date you want to see before and after or do you want to see last week verus this week or do you want somewhere you can pick the date/week/month/year from?