How to add a date slicer to existing report with measures

I built a report that has many measures for current month to date. 50 plus measures. Now the customer would like to add a date slicer. Some of my measures go three and four deep, built from the previous measure.

Since the context of all of my measures was based on the data loaded into the table (current month to date) I did not put any filtering for a date slicer in my DAX expressions. When I wrote the expressions, I hadn’t even started the visualization.

Is it possible to add date slicers values to existing measures after the fact?

@LASHBURN,

It’s absolutely possible. You’ll build measures to harvest the date slicers (e.g., VALUES(Dates[Date]), SELECTEDVALUE( Dates[Date] ), etc.) and then insert these in the appropriate places in your existing measures - most commonly in the filter conditions of CALCULATE statements.

If you’re comfortable with either DAX Studio or Tabular Editor, I would highly recommend using them for this task, as they are much better for search and replace, identifying measure dependencies and testing measure results than the crappy native DAX editor.

I did a video on date harvest deep dive a while back that you may find helpful in this task.

Finally, if you’re able to post a PBIX file, we will be able to provide you more specific guidance.

I hope this is helpful.

  • Brian

Hi Brian, I’ll take a look in the morning, much appreciated!

@LASHBURN,

FYI – per my prior message, here’s a short video on how to trace measure dependencies using DAX Studio:

Here’s how to do the same thing in Tabular Editor:


From: https://docs.tabulareditor.com/Advanced-features.html

I hope this is helpful.

  • Brian
1 Like

This may be a repeat, I don’t see my reply I just sent.
Is there a way to share my pbix with just you and not have it available to the public?

I built a worksheet last night of the measures and their dependencies beginning at the far right.
I have other visualizations on the page of the report that I would not want to be affected by a date slider.

So I think using the harvested dates for current billing period in the measures will be the best way to go.Daily Report Revenue Measures.xlsx (12.9 KB) Revenue measures

@LASHBURN,

Per @sam.mckay’s rules of the forum, all business should be conducted on the forum itself, not offline - the reason being that the dialogue and solutions are not just for the original poster, but intended to serve as a longer-term repository/reference for others with similar problems.

Thus, the best approach is typically to provide a representative PBIX file with the sensitive information masked. Here’s a video I did a while back with some simple techniques for how to do that. If you are able to provide a masked file, I will be able to provide you much more specific support than I’ve been able to thus far.

In terms of visualizations you don’t want affected by a date slicer/slider, by far the easiest way to address that is via the “Edit Interactions” options:

In looking at the excellent dependency table you put together, this may not be as hard as it might originally appear. I think it’s going to be a matter of sifting through the dependency table and replacing the hardwired billing periods, such as the one highlighted below with your date slicer harvest measure, e.g.,[Harvest Bill Date] <= Yesterday, and then testing all the measures that are dependent on that one.

  • Brian

That’s good news! I was in the middle of anonomizing the data. I’m going to finish that up shortly.

@LASHBURN,

That’s great - if you have some confirmed results we can validate against, we should be able to knock this out today.

  • Brian

I got all the way to the end of anonomizing the tables, could not find the Copy Table option, went ahead and copied the data from Transform and it did not copy my measures. Wasted two hours. I am so discouraged. I guess I will take one measure at a time and try adding the filters that way.

BP Count of Credit Quantity PLT =
VAR BillingStartDate = [Current Billing Start Date]
VAR BillingEndDate = [Current Billing End Date]
RETURN
CALCULATE(
COUNT( ‘billing_activities’[Quantity] )
,billing_activities[Transaction Type] = “Credit”
,billing_activities[Billing Code] = “PLT”,
DATESBETWEEN(
billing_activities[Bill Date],
BillingStartDate,
BillingEndDate)
)

What do I need to add to make it zero instead of Blank?

I have found that just adding “+0” at the end of a function will force it to return a zero instead of a blank. Without spending a lot of time trying to understand your measure above, I’d say add the “+0” either after the COUNT() function or the CALCULATE() function.

Hope that helps!

@LASHBURN,

A few things:

To access the Copy Table option, you need to be in the data view (see screenshot below).

In your measure above, for DATESBETWEEN, the first parameter needs to be a column of contiguous dates. Typically this is the Dates[Date] column, though it doesn’t have to be. However if that column is not contiguous it will not return an accurate result.

An alternative to DaveC’s approach to replacing blank with zero is to do your primary calculation in a variable (e.g., Result) and then

RETURN
COALESCE( Result, 0 )

– Brian

where do I put the RETURN
COALESCE( Result, 0 )
in my expression?

@LASHBURN,

BP Count of Credit Quantity PLT =

VAR BillingStartDate = [Current Billing Start Date]
VAR BillingEndDate = [Current Billing End Date]
VAR Result =
    CALCULATE (
        COUNT ( billing_activities[Quantity] ),
        billing_activities[Transaction Type] = "Credit",
        billing_activities[Billing Code] = "PLT",
        DATESBETWEEN ( billing_activities[Bill Date], BillingStartDate, BillingEndDate )
    )
RETURN
    COALESCE ( Result, 0 )

thank you! Moving forward

How do I add the date filter for an expression like this?

BP Actual Billings CCP = IF(billing_activities[Billing Code] IN {“CCP”},billing_activities[Total],0)

@LASHBURN,

Wrap a CALCULATE () function around your IF statement, and set your date filter as the second argument of the CALCULATE statement.

– Brian

BP Actual Billings CCP =
VAR BillingStartDate = [Current Billing Start Date]
VAR BillingEndDate = [Current Billing End Date]
VAR Result =
CALCULATE (
IF(billing_activities[Billing Code] IN {“CCP”},billing_activities[Total],0)
,
DATESBETWEEN(
Dates[Date],
BillingStartDate,
BillingEndDate)
)

invalid token error

@LASHBURN,

I think that’s typically a syntax error related to the table names. Try redoing the IF statement by selecting tables and fields using IntelliSense to make sure you have the exact syntax right. Also where is your RETURN statement?

– Brian

I lost the field to bring in because its not a measure. Only the measures are available.