Dynamic SUM IF Using Calculate

[USABB_Data]

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

[v-xicai · Accepted solution]

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]

Incredible, it worked perfectly!!  Thanks!!