Forum Discussion

USABB_Data's avatar
USABB_Data
Helper II
7 years ago
Solved

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_IDFiscal PeriodSales
ATL110
ATL220
ATL330
ATL440

 

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])
Need Help
Show and Tell
  • v-xicai's avatar
    v-xicai
    7 years ago

    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

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    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.