cancel
Showing results for
Search instead for
Did you mean:
Highlighted
USABB_Data 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.

USABB_Data Frequent Visitor

## Re: Dynamic SUM IF Using Calculate

Incredible, it worked perfectly!!  Thanks!!

## Helpful resources

Announcements Top Ideas Top Kudoed Authors
Users Online
Currently online: 38 members 1,037 guests
Recent signins:
• • umanbr • powerbiblogic • sktneer • Skittle0302 • RiccardoBefore2 • stewartfiserv Please welcome our newest community members:
• Jayson01 • rodoramco_2020 • thangtruong • alvinyong • foxyspace • mizfire • blizzardice 