topic Re: CALCULATE function does not filter data in Report Server

CALCULATE function does not filter data

SaiSpandana2811 — Thu, 24 Feb 2022 15:34:54 GMT

I am trying to use the CALCULATE function in DAX to find sum of income in last 12 months. The measure I am using is as below

Sub measures

Latest Month in Table = MAX(pbi_inc[yymm])
Samemonth_prev year = [Latest Month in Table] -100

Main DAX:

income_inc=
var lm = [Latest Month in Table]

var r12m = [ Samemonth_prev year]

return CALCULATE(SUM(pbi_inc[inc], pbi_inc[ic/cr] =1,pbi_inc[yymm] >=lm , pbi_inc[yymm] <=r12m)

The issue I am facing is the Calculate function is not filtering data for yymm. It filters data when I change the ic/cr value but not for yymm.

It works fine when I hardcode to something like 2112 but not when I use variables and submeasures.

I have compared the data types and spaces and all are compatible. All fields are taken from the same fact table

Can some one please help me?

lbendlin — Sat, 26 Feb 2022 00:59:24 GMT

You may be overcomplicating things a bit. Read about the EDATE() function. Also, your comparators are the wrong way round.

Instead of

pbi_inc[yymm] >=lm , pbi_inc[yymm] <=r12m

you should use

pbi_inc[yymm] <=lm , pbi_inc[yymm] >=r12m

v-henryk-mstf — Tue, 01 Mar 2022 06:43:35 GMT

I think you can try to use the DATESINPERIOD function to quickly solve for the sum over the past period of dates. Refer to the following formula:

M_ = CALCULATE ( SUM ( 'Table'[Value] ), DATESINPERIOD ( 'Table'[Date], MAX ( 'Table'[Date] ), -12, MONTH ) )

Or create a date column and dynamically filter a date to find the sum over time.

M_1 = CALCULATE ( SUM ( 'Table'[Value] ), DATESINPERIOD ( 'Table'[Date], SELECTEDVALUE ( Tab_data[Date] ), -12, MONTH ) )

Also you can read related blog for more details:

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.