PowerBI DAX - Using a measure as a filter within a CALCULATE function

0
votes

I want to create a DAX measure in PowerBI that will provide an aggregate of sales on a specific date. I need that date to be controlled by a date slicer. Specifically the the maximum date on the slicer.

I would expect this to be a Calculate() function. So something like the following if it was hard coded with a date. 

=CALCULATE(SUM(FactInternetSales2[Sales]), DimDate2[Dim Date] = DATE(2018, 06, 18))

But I need the filter component of the the Calculate() function (i.e , DimDate2[Dim Date] = DATE(2018,06,18)) to be dynamically populated from the max date on the date slicer. I understand, however that a measure can't be used as a filter in a calculate function - so I can't create a measure such as follows to identify the maximum date 

=LASTDATE(DimDate2[Dim Date])

and then use it in the Calculate() function such as 

=CALCULATE(SUM(FactInternetSales2[Sales]), DimDate2[Dim Date] = LASTDATE(DimDate2[Dim Date])

Can anyone outline how I can use the maximum date from the slicer to filter the Calculate() function, or achieve the same outcome?

A copy of my working file is located here https://drive.google.com/file/d/1d1JiyPm1jOD9XkVqv3Q5pm0vk1FMotH9/view?usp=sharing

Cheers Steve

2
powerbidax

2 Answers

2
votes

You can read in the parameter value into a variable.

SalesSum =
VAR EndDate = LASTDATE ( DimDate2[Dim Date] )
RETURN
    CALCULATE ( SUM ( FactInternetSales2[Sales] ), DimDate2[Dim Date] = EndDate )
-1
votes

You can use the Calculate() function with a filter like this:

=CALCULATE(SUM(Table1[SalesValue]), FILTER(Table1, Table1[Year] = 2019))

To get the value from a slicer you go to Modeling > New Parameter. Here you can specifie your parameter needs and hit OK. Now you gat a new table and column for this parameter on the fields pane. Just reference now on this column with the following code:

=SELECTEDVALUE(ParameterTable[ParameterValue])