cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Dynamic SUM IF Using Calculate

Hi,

I am looking to create a dynamic Sum If measure but cannot get it right.  I saw this topic, remove link because would not let me post , but still am having trouble.

Data

 SLS_ID Fiscal Period Sales ATL 1 10 ATL 2 20 ATL 3 30 ATL 4 40

Since we are currently in Fiscal Period 4, I only want to sum periods 1-3 to get 60.  Here is what I have but I don't want a hardcode of "4" in there:

MF YTM = CALCULATE(SUM([Sales]),[Fiscal Period]<4).

I tried the following but it gave an error: MF YTM = CALCULATE(SUM([Sales]),[Fiscal Period]<[Current Fiscal Period])
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Dynamic SUM IF Using Calculate

Hi @USABB_Data ,

You can try to create measure like DAX below.

MF YTM = CALCULATE(SUM(Table1[Sales]),FILTER(ALLSELECTED(Table1),Table1[SLS_ID]=MAX(Table1[SLS_ID])&&Table1[Fiscal Period]<MAX(Table1[Fiscal Period])))

Best Regards,

Amy

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

2 REPLIES 2
Community Support Team

## Re: Dynamic SUM IF Using Calculate

Hi @USABB_Data ,

You can try to create measure like DAX below.

MF YTM = CALCULATE(SUM(Table1[Sales]),FILTER(ALLSELECTED(Table1),Table1[SLS_ID]=MAX(Table1[SLS_ID])&&Table1[Fiscal Period]<MAX(Table1[Fiscal Period])))

Best Regards,

Amy

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

Frequent Visitor

## Re: Dynamic SUM IF Using Calculate

Incredible, it worked perfectly!!  Thanks!!

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 38 members 1,037 guests
Recent signins: