cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thejeswar Senior Member
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

YearReportung MonthRevenueActualsDatekeyMonthMonth Name
2018Feb7493781297602/1/20182February
2018Feb1352053327672/1/20182February
2018Feb168502574702/1/20182February
2018Feb5474423327502/1/20182February
2018Mar7481551285373/1/20183March
2018Mar1339823315443/1/20183March
2018Mar167279562473/1/20183March
2018Mar5462193315273/1/20183March
2018Apr7592431396254/1/20184April
2018Apr1450703426324/1/20184April
2018Apr178367673354/1/20184April
2018Apr5573073426154/1/20184April
2017Dec65983212352512/1/201712December
2017Dec4565932653212/1/201712December
2017Dec789565123512/1/201712December
2017Dec45789632651512/1/201712December
2017Nov65860912230211/1/201711November
2017Nov4443632530911/1/201711November
2017Nov777335001211/1/201711November
2017Nov45667332529211/1/201711November
2017Oct66969713339010/1/201710October
2017Oct5552433639710/1/201710October
2017Oct888216110010/1/201710October
2017Oct46776133638010/1/201710October

 

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.

 

Year selected.PNG

 

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. 

Month Selected.PNG

 

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
Floriankx Established Member
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
Floriankx Established Member
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.

 

You can add: 

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. 

Thejeswar Senior Member
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!!!

Floriankx Established Member
Established Member

Re: Filter with calculated measures

Hello,

 

you are clear.

What happens actually if you select a month via slicer?

Highlighted
Thejeswar Senior Member
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

Floriankx Established Member
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)
)
)

 

Thejeswar Senior Member
Senior Member

Re: Filter with calculated measures

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

niketmistry7 Frequent Visitor
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.

Capture.PNG

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.

Can anyone please help me in this?