Dax calculate help || Power BI

[maverickf17]

Hi All,
I just wanted to understand the behavior of calculate.

1) 

The below is the dax

1) calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

2) 

month_selection = SELECTEDVALUE('calendar'[month_no],0)

--------------------------------------------------------------------
The month selection is being supplied to the calculate
-------------------------------------------
What I am trying to achieve?
I am trying to achive dynamic selectionof month value supplied by the user and calculate should compute the new subcribers.

I tried 
1) 

Sum_new_subscriber = SUM(net_subscriber_growth[NewSubscribers])

2)1) calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

2) 

month_selection = SELECTEDVALUE('calendar'[month_no],0)


Both the approaches produces the correct output.
I wanted to understand If any user does not make a seldction in slicer than 0 will be supplied to calculate filter but 
-> It produces some values in the table marked in blue.

for example:

Can anyone help me understand how these values are produced?
--------------------------------------------
Also I wanted to know how do we compute new subscribers for previous month by tweaking this calculate.

calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))


Thank you

[FBergamaschi · Accepted solution]

Hi,

answers are here

I just wanted to understand the behavior of calculate.

1) 

The below is the dax

1) calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

2) 

month_selection = SELECTEDVALUE('calendar'[month_no],0)

--------------------------------------------------------------------
The month selection is being supplied to the calculate
-------------------------------------------
What I am trying to achieve?
I am trying to achive dynamic selectionof month value supplied by the user and calculate should compute the new subcribers.

I tried 
1) 

Sum_new_subscriber = SUM(net_subscriber_growth[NewSubscribers])

2)1) calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

2) 

month_selection = SELECTEDVALUE('calendar'[month_no],0)


Both the approaches produces the correct output.
I wanted to understand If any user does not make a seldction in slicer than 0 will be supplied to calculate filter but 
-> It produces some values in the table marked in blue.

for example:

Can anyone help me understand how these values are produced?

Answer: the problem is that even though the user made no selection (so [month selection] you would think returns 0, the month is in the context of the matrix, so [month selection] returns the visible month and a simple sum is like the CALCULATE statement you wrote), if you want to see nothing if the user selects more than one month change the code of the CALCULATE measure as follows (ALLSELECTED will ignore the month from the visual and only take it from the slicer)

calculate_new_subs =

VAR selection =CALCULATE( [month_selection], ALLSELECTED( 'calendar' ) )

RETURN

CALCULATE(sum(net_subscriber_growth[NewSubscribers]),'calendar'[month_no] = Selection)

--------------------------------------------
Also I wanted to know how do we compute new subscribers for previous month by tweaking this calculate.

calculate_new_subs = CALCULATE(sum(net_subscriber_growth[NewSubscribers]),FILTER(net_subscriber_growth,MONTH(net_subscriber_growth[Month]) = [month_selection]))

Answer you can use this code

calculate_new_subs_2 PM =

VAR selection = CALCULATE( SELECTEDVALUE( 'calendar'[YearMonthNr] ), ALLSELECTED( 'calendar' ) )

RETURN

CALCULATE(sum(net_subscriber_growth[NewSubscribers]),'calendar'[YearMonthNr] = Selection-1, REMOVEFILTERS( 'calendar') )

but before you must have added two columns to the calendar

Year = YEAR([Date]) (so you can include a lsicer to select a single year in the visuals (never use only the month as you might have multiple years in a single month)

YearMonthNr = YEAR([Date])*12+month([Date])

If shared the pbix via mail

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Greg_Deckler]

@maverickf17 Good luck trying to understand the black box inner workings of CALCULATE. However, what is happening is that even though no months are selected in your slicer, the month is still being filtered in the table visual so month_selection is returning the selected month in the context of the table visual itself and hence your result. But, as you can see from your results, the CALCULATE function is wholly unnecessary and you don't need it at all. This is almost always the case, you really never have to use CALCULATE.