How to change default visual range under slicer?

[YunJ]

Hi,

I used Store and Campaign as slicer in my dashboard. For example in this case, when Store=LRL FSS, Campaign=618, the Date start from 2020/5/25 to 2020/6/23.

However, I want to change the date range (I don't want to change raw data), which means when Store=LRL FSS, Campaign=618, the Date will start from 2020/6/1 to 2020/6/30. But here you can see the end date of my new visual still is 2020/6/23.

Please find pbix file: 

https://www.dropbox.com/s/bow8o2210vwff0z/test%20%282%29.pbix?dl=0

Thanks a lot for your time!

[Anonymous]

Hi @YunJ ,

As checked the data in table EC_campaign, it will only include the data with date from 5/25/2020 to 6/23/2020 when select the store is LRL FSS and the campaign is 618. Hence it will not display the data which date is after 6/23/2020... 

I updated your sample pbix file(see the page named "Duplicate of Page 2" in the attachment), please check whether that is what you want.

1. Delete the relationship between table Date and  EC_campaign

2. Create a date range slicer: put Date field from table Date on the slicer

3. Create another new measure to get  the spending and applied this new measure to replace the original Spending field on the visual

Nspending = 
CALCULATE (
    SUM ( 'EC_campaign'[Spending] ),
    FILTER ( 'EC_campaign', 'EC_campaign'[Date] = SELECTEDVALUE ( 'Date'[Date] ) )
)

Best Regards

[YunJ]

Hi @Anonymous 

I only want two slicers on the dashboard: Store and Campaign.

For example, when Store="LRL FSS" and Campaign="618", the date will be from 2020.06.01 to 2020.06.30.

when Store="LRL MU" and Campaign="38", the date will be from 2020.03.03 to 2020.03.20.

When other values selected, the date will also change based on specified condition.

Thanks a lot for your time.

[Anonymous]

Hi @YunJ ,

Could you please provide more details and explanation about the below 2 situations?

when Store="LRL FSS" and Campaign="618", the date will be from 2020.06.01 to 2020.06.30.

when Store="LRL MU" and Campaign="38", the date will be from 2020.03.03 to 2020.03.20.

For example: when Store="LRL MU" and Campaign="38"

Why the data range is from 2020.03.03 to 2020.03.20 not other one? Why it is not from 2020.2.24 to 2020.3.8? Is there any special reason or condition need to be met?

Best Regards

[YunJ]

Hi @Anonymous 

Thanks for your reply. 

In my dashboard, I have different visuals.

For some visuals, I can just use the default date in the dataset (which means in these visuals, for example when Store="LRL MU" and Campaign="38", the date will be from 2020.2.24 to 2020.3.8).

But in other visuals, I don't want use these date, so for example, when Store="LRL MU" and Campaign="38", I want the data range is from 2020.03.03 to 2020.03.20.

I can complete this by using a doubled data which contains different date, but I feel a little tricky. That's why I want to complete this by using measure.

[Anonymous · Accepted solution]

Hi @YunJ ,

I updated the pbix file, please check whether that is what you want.

1. Create a dimension table Campaigns, it include Store,  Campaign, Campaign start data and Campaign end date

Note: the data in the dimension table is just mockup data, you can fill it with the data which suitable for your scenario

2. Update the formula of measure Nspending

Nspending = 
VAR _mindate=CALCULATE(MAX('Campaigns'[Start]),FILTER('Campaigns','Campaigns'[Store]=SELECTEDVALUE('EC_campaign'[Store])&&'Campaigns'[Campaign]=SELECTEDVALUE('EC_campaign'[Campaign])))
var _maxdate=CALCULATE(MAX('Campaigns'[End]),FILTER('Campaigns','Campaigns'[Store]=SELECTEDVALUE('EC_campaign'[Store])&&'Campaigns'[Campaign]=SELECTEDVALUE('EC_campaign'[Campaign])))
return
CALCULATE (
    SUM ( 'EC_campaign'[Spending] ),
    FILTER ( 'EC_campaign', 'EC_campaign'[Date] >=_mindate &&'EC_campaign'[Date]<=_maxdate ) 
)

Best Regards

[YunJ]

Thanks a lot @Anonymous . That's what I want:)

[MFelix]

Hi @YunJ ,

Do you want to filter out the information or replace the Campaign date by the one in the formula? 

You are using a measure and that is a dinamic calculation so the date will appear but with a sum of 0.

[YunJ]

Hi @MFelix 

I want to replace the Campaign date by the one in the formula, which means in this situation, I want the date shown from 2020/6/1 to 2020/6/30.

Thanks for your time.