Create a variable to act as a filter within calculate

[Reigning_data]

Hi All,

Apologies if this has been answered before, I have tried to google it but I'm not sure how to pose the question.

My issue is this:

Say I have several measures that need to do the same thing, on different data sources.

Example would be

Revenue = CALCULATE ( SUM ( column1 ) ,

DATESBETWEEN ( calendar , DATE ( 2019,01,01 ) , DATE ( 2019,02,01 )

))

What I would like to do is to create the DATESBETWEEN filter as it's own variable, seperate from the measure so that, in the eventuality that the DATESBETWEEN period needs changing, I would only need to change it once, not for every measure. Something like below is what I had in mind.

Period = DATESBETWEEN ( calendar , DATE ( 2019,01,01 ) , DATE ( 2019,02,01 ) )

Revenue = CALCULATE( SUM ( column1 ) , [Period] )

Many thanks,

[OwenAuger · Accepted solution]

Hi @Reigning_data 

• Does your Period table need to respond to filter context in any way?
  If so, then you can't currently do what you've described in a model built in Power BI Desktop. However, you can do this sort of thing Azure Analysis Services, this should eventually be possible in Power BI.
  See these articles on DETAILROWS and Calculation Groups:
  
  • https://www.sqlbi.com/articles/creating-table-functions-in-dax-using-detailrows/
  • https://www.sqlbi.com/blog/marco/2019/03/01/calculation-groups-in-dax-first-impressions/
  • https://blogs.msdn.microsoft.com/analysisservices/2019/03/01/whats-new-for-sql-server-2019-analysis-...
• If your Period table doesn't need to respond to filter context (e.g. if it's always a fixed date range), then you could use this workaround:
  1. Create a DAX calculated table called Period using the expression such as in your original post using DATESBETWEEN or some other method.
  2. Since the Period calculated table won't retain lineage, you can use TREATAS to apply the Period table as a date filter within measures, for example:
     

     Revenue =
     CALCULATE(
         SUM ( column1 ),
         TREATAS ( Period, 'Date'[Date] )
     )

Regards,

Owen