Doubt with Date Ranges

[Anonymous]

Hi all,

I have one question,

I'm doing a Dashboard for the marketing campaign to analyse the campaigns they do. So each campaign has start and end date. 

What I want is to select in a filter one of these campaigns and the report must filter dinamically based on start and end date of the campaign.

Now I have to filter by date manually because what I'm doing is to look to sales table filtering by the clients who have bought in the campaign.

Any idea?

Thx!

[d_gosbell]

So you have not really given us much information about your model. But assuming that you have a Sales table that has a date column which is related to a date table and you have a Campaigns table which is not related to either of these other two tables, then you could use a calculation like the following:

Total Sales =
VAR _selectedCampaign =
    SELECTEDVALUE ( Campaign[Campaign] )
RETURN
    IF (
        ISBLANK ( _selectedCampaign ),
        SUM ( Sales[Amount] ),
        CALCULATE (
            SUM ( Sales[Amount] ),
            KEEPFILTERS (
                DATESBETWEEN (
                    'Date'[Date],
                    SELECTEDVALUE ( Campaign[StartDate] ),
                    SELECTEDVALUE ( Campaign[EndDate] )
                )
            )
        )
    )

[Anonymous]

Hi @d_gosbell Thats a good point. Your solution sounds nice but I will tell you a little more about my model regarding a better aproach to an optimal solution.

For your understanding I've a Sales table connected with a clients table witd ID_CLIENT. Also I've a Table with the campaign code and all the clients who have bought in the campaign connected with ID_CLIENT, so my relation field is ID_CLIENT. When I choose a campaign in the filter, the ID_CLIENT table filter the sales table only with the ID_CLIENT sales in the campaign, but I've also to filter manually start and end date. 

My idea was to calculate a measure or something like that in order to filter all the report by these dates, not only the total sales measure.

Thanks for your support!

[d_gosbell · Accepted solution]

---

@Anonymous wrote:

My idea was to calculate a measure or something like that in order to filter all the report by these dates, not only the total sales measure.

---

So that is possible. You could use a measure like the following which will return the number of dates in the current context which are between the start/end dates for the selected campaigns

IsCampaignDate = 
VAR _selectedCampaign =
    SELECTEDVALUE ( Campaign[Campaign] )
RETURN
    IF (
        ISBLANK ( _selectedCampaign ),
        COUNTROWS( 'Date' ),
        CALCULATE (
            COUNTROWS( 'Date' ),
            KEEPFILTERS (
                DATESBETWEEN (
                    'Date'[Date],
                    SELECTEDVALUE ( Campaign[StartDate] ),
                    SELECTEDVALUE ( Campaign[EndDate] )
                )
            )
        )
    )

But there are 2 issues with this approach:

1. This sort of filter has to be measure based, but you can't currently apply a filter using a measure in the report or page level filters. (so you have to apply this filter to every visual one at a time)
2. It only works properly if you have individual dates displayed on your visual. If you go up to a higher level (eg. Month or Year) the filter will return all sales for the month even if the campaign only ran for the last 5 days of the month (the previous approach of applying the filter within the Sales measure does not suffer from this limitation, but you have to repeat the same logic across multiple measures)