Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
SaiSpandana2811
Regular Visitor

CALCULATE function does not filter data

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?

 

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

Hi @SaiSpandana2811 ,

 

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 )
)

vhenrykmstf_0-1646116693552.png

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 )
)

vhenrykmstf_1-1646116918901.png

Also you can read related blog for more details:

5 ways to calculate last 12 months in DAX for Power BI - BI Gorilla

Solved: How to calculate last 12 months - Microsoft Power BI Community

 

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.

View solution in original post

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

Hi @SaiSpandana2811 ,

 

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 )
)

vhenrykmstf_0-1646116693552.png

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 )
)

vhenrykmstf_1-1646116918901.png

Also you can read related blog for more details:

5 ways to calculate last 12 months in DAX for Power BI - BI Gorilla

Solved: How to calculate last 12 months - Microsoft Power BI Community

 

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.

lbendlin
Super User
Super User

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.