cancel
Showing results for
Did you mean:
Senior Member

## Filter with calculated measures

Hello guys,

I have been trying to create a calculated measure which should be filtered based on another measure. Here's my scenario

1. I want to create Total YTD of a column which I am doing by below snippet

column = Apr18[Revenue] - Apr18[Actuals]
YTD Revenue = TOTALYTD(SUM(Apr18[column]),Apr18[Datekey])

The Above code gives me the YTD Revenue when none of the slicers are selected. But in my case, I have two slicers which are Year and Month based on which the YTD Revenue measure has to get modified.

2. For this purpose I created the following two measures using SELECTED VALUE DAX as shown below

SelectedYear = SELECTEDVALUE(Apr18[Year])
SelectedMonth = SELECTEDVALUE(Apr18[Month])

Using the above two measures I tried to filter the YTD Revenue measure. I tried different approaches (even those from various others posts in the forum) whose codes as follows. But nothing seem to work when I make a selection in the slicers for Year and Month

YTD Revenue = TOTALYTD(SUM(Apr18[column]),Apr18[Datekey],FILTER(Apr18,Apr18[Year]=SELECTEDVALUE(Apr18[Year]) && Apr18[Month]<=[SelectedMonth]))

Then I brought the Slected Month as  a Column and tried using that as well as given below

YTD Revenue = TOTALYTD(SUM(Apr18[column]),Apr18[Datekey],FILTER(Apr18,Apr18[Year]=SELECTEDVALUE(Apr18[Year]) && Apr18[Month]<=Apr18[filtermonth]))

The following codes also I tried but with no needed result

YTD Revenue =
var currYear= MAX(Apr18[Year])
var currMonth=Max(Apr18[Month])
return
if(AND(currYear=[SelectedYear],currMonth<=[SelectedMonth]),
CALCULATE(TOTALYTD(SUM(Apr18[column]),Apr18[Datekey]),FILTER(Apr18,Apr18[Year]=SELECTEDVALUE(Apr18[Year]) && Apr18[Month]<=MAX(Apr18[filtermonth]))))
YTD Revenue =
var currYear= MAX(Apr18[Year])
var currMonth=Max(Apr18[Month])
return
if(AND(currYear=[SelectedYear],currMonth=[SelectedMonth]),
CALCULATE(TOTALYTD(SUM(Apr18[column]),Apr18[Datekey])))
YTD Revenue =
var y = [SelectedYear]
var m = [SelectedMonth]
RETURN
TOTALYTD(SUM(Apr18[column]),Apr18[Datekey],FILTER(Apr18,Apr18[Year]=y && Apr18[Month]<=m))

All these measures are getting filtered by month whenever I make a selection in the month column

Here's the look at my data

 Year Reportung Month Revenue Actuals Datekey Month Month Name 2018 Feb 749378 129760 2/1/2018 2 February 2018 Feb 135205 332767 2/1/2018 2 February 2018 Feb 168502 57470 2/1/2018 2 February 2018 Feb 547442 332750 2/1/2018 2 February 2018 Mar 748155 128537 3/1/2018 3 March 2018 Mar 133982 331544 3/1/2018 3 March 2018 Mar 167279 56247 3/1/2018 3 March 2018 Mar 546219 331527 3/1/2018 3 March 2018 Apr 759243 139625 4/1/2018 4 April 2018 Apr 145070 342632 4/1/2018 4 April 2018 Apr 178367 67335 4/1/2018 4 April 2018 Apr 557307 342615 4/1/2018 4 April 2017 Dec 659832 123525 12/1/2017 12 December 2017 Dec 45659 326532 12/1/2017 12 December 2017 Dec 78956 51235 12/1/2017 12 December 2017 Dec 457896 326515 12/1/2017 12 December 2017 Nov 658609 122302 11/1/2017 11 November 2017 Nov 44436 325309 11/1/2017 11 November 2017 Nov 77733 50012 11/1/2017 11 November 2017 Nov 456673 325292 11/1/2017 11 November 2017 Oct 669697 133390 10/1/2017 10 October 2017 Oct 55524 336397 10/1/2017 10 October 2017 Oct 88821 61100 10/1/2017 10 October 2017 Oct 467761 336380 10/1/2017 10 October

The Below are the screenshots of my PBI Desktop.

Only when year is selected, the report data looks fine. In this case, the YTD Revenue in the card is the sum of column value for Feb, Mar and Apr.

But when a month is selected, the card gets filtered for that month. Ideally the way that I am expecting it to work is, when Mar is selected, the measure in the card should show me the sum of values of column for the months of Feb and Mar and when Feb is selected, it should show the value of only Feb. The Case is similar for 2017 as well

i.e. as highlighted, when Mar is selected, the card should display 1495560 and not 747780.

Any help here is appreciated. Thanks in advance.

Hope the information I have provided is sufficient enough for helping me

Note: The Card works this way when we apply advanced filter of less than or equal in Page level filters. But unfortunately I can't use that in my case

1 ACCEPTED SOLUTION

Accepted Solutions
Established Member

## Re: Filter with calculated measures

Hello,

What probably would work perfectly is a separate DateTable with contiguous Dates. Then you could add the DateTable columns to your PivotTable and use it for TOTALYTD.

After trying a little bit this formula gave me the correct result as well:

YTD Revenue:=VAR LatestDate=MAX(FactTable[Datekey])
RETURN
CALCULATE(SUM([column]);
CALCULATETABLE(
FILTER(FactTable;FactTable[Datekey]<=LatestDate&&YEAR([Datekey])=YEAR(LatestDate));
ALL(FactTable)
)
)

7 REPLIES 7
Established Member

## Re: Filter with calculated measures

Hello,

maybe I haven't understood your problem totally.

My first question is why don't you add a timeline instead of separate slicers for month and year?

Revenue YTD=TOTALYTD([Measure],Table[DateKey])

If you want to use slicer instead of timeline.

Revenue YTD=IF(HASONEVALUE(Table[Year],
TOTALYTD([Measure],Table[DateKey]),
BLANK())

So it should automatically give you your YTD value if only one year is selected.

Senior Member

## Re: Filter with calculated measures

Hi,

I am fine with using the below code snippet

Revenue YTD=TOTALYTD([Measure],Table[DateKey])

And this is also giving YTD values for the year selections.

i.e. Say I am having 3 years.

2016

2017

2018

When I select 2018, this gives me YTD values for 2018, When 2017 is selected, YTD values for 2017 is shown and so on.

But my requirement is in addition to the year selection, it should also respond to the month selections made

The Following is the step-by-step process of potential selection of slicers

i.e.

1. Firstly, If 2018 and Apr are selected in slicers, my YTD Value should be the Value from Jan-2018 to Apr-2018

2. Now if my Month slicer is changed to Mar, my YTD Value should be from Jan-2018 to Mar-2018

3. Lastly, If I change my year to 2017, the card should display YTD Value from Jan-2017 to Mar-2017, since I have already selected my Month to March in the previous step.

Hope I am clear this time!!!

Highlighted
Established Member

## Re: Filter with calculated measures

Hello,

you are clear.

What happens actually if you select a month via slicer?

Senior Member

## Re: Filter with calculated measures

Hi,

The Measure gets filtered for the Month, instead of bringing all the data of months less than that month

Please refer the screenshot posted above in the post for the same

Established Member

## Re: Filter with calculated measures

Hello,

What probably would work perfectly is a separate DateTable with contiguous Dates. Then you could add the DateTable columns to your PivotTable and use it for TOTALYTD.

After trying a little bit this formula gave me the correct result as well:

YTD Revenue:=VAR LatestDate=MAX(FactTable[Datekey])
RETURN
CALCULATE(SUM([column]);
CALCULATETABLE(
FILTER(FactTable;FactTable[Datekey]<=LatestDate&&YEAR([Datekey])=YEAR(LatestDate));
ALL(FactTable)
)
)

Senior Member

## Re: Filter with calculated measures

This thing works fine. Can you pls. explain this DAX Usage?

Frequent Visitor

## Re: Filter with calculated measures

Hi, I have a similar question with text filter and I know it's pretty stupid but I am not able to figure out a simple thing to do in DAX.

In the image above, I want to calculate Weighted Sales filtered on Types

Current formula for Weighted Sales = Sales/ Sum(Sales)

I am trying to do Weighted Sales = Sales/ [Sum(Sales), filtered by (Type)] - I want to see the weighted sales by types.

Whenever I am using DAX, I need to use Divide function and for numerators and denominators in it, I have either use SUM() or similar funcitons to perform calculation, I cannot do simple Column1/Column2.