Create Measure using Slicer and related Date

[Anonymous]

Hello Everyone,

I have tried to find a related solution but have been unable to find anything suitable. Apologies if there is already a solution out there for this.

I am trying to create a measure which uses a Slicer and then a corresponding date to provide a filtered output.

I have a Data table which has Date, Country, Time and Volume. The measure I would like to create is Time/Volume. In another Table I have Country in column A and Launch Date in column B. I would like to use a slicer to select the Country and then have two measures which use the Launch Date to provide the outcome of the calculation for before the launch date and after.

Date	Country	Time	Volume
30/6/20	France	5	100
1/7/20	France	7	200
2/7/20	France	10	400

Country	Launch Date
UK	24/6/20
France	1/7/20
Belgium	6/7/20

Example: I select the country France, which has a launch date of 1/7/20. Measure 1 provides the Time/Volume for before 1/7/20 and Measure 2 provides the Time/Volume for on and after 1/7/20.

Thanks for all your support!

[Stachu · Accepted solution]

you can try something like this:

Measure 1 = 
VAR __launchDate = MAX('Launch'[Launch Date])
RETURN
CALCULATE(SUM(Data[Volume]),Data[Date]<__launchDate)

but I would strongly recommend to create a separate tables for Countries and Calendar (to follow a star schema https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/)

Countries would be joining 1:Many with both Data and Launch tables, and Calendar joining with Data.

Then the code could look like this

Measure 1 = 
VAR __launchDate = MAX('Launch'[Launch Date])
RETURN
CALCULATE(SUM(Data[Volume]),Calendar[Date]<__launchDate)

[Anonymous]

Thanks @Stachu, I will try this out.

I actually have separate dimension tables for both Date and Country which i use for filtering or Slicers. Not sure if that makes a difference here. I do need to have the table which lists the launch date for all countries though.

[Stachu]

That's perfect, then the measure I posted will work, as long as you don't have a join between Calendar and Launch tables (when you filter Calendar it will push its filter context to Launch, and that's not what you want)

[Anonymous]

Thank you, it has all worked perfectly!