Combining counta, sum, and datebetween functions in a column help

[Anonymous]

Hi I would like this formula for a column to count all provider names that have an application sum of 0 between 7/1/17-7/31/17.

If the sum is zero I would like to put a label that provider name saying "lapse" . Once I have this baseline, the goal is to create formulas on top of this to show whether they've lapsed for 1, 2, 3 months, etc.

How can I do this? 

CALCULATE ( counta ( LNApps_ProviderDim[Provider_Name] ), 'LNApps_Facts'[Total_Application_Amt] =0,DATESBETWEEN(LNApps_AppDateDim[ActualDate],  
    DATE(2017,7,1),  
    DATE(2017,7,30)))

[Greg_Deckler]

IF(CALCULATE ( counta ( LNApps_ProviderDim[Provider_Name] ), 'LNApps_Facts'[Total_Application_Amt] =0,DATESBETWEEN(LNApps_AppDateDim[ActualDate],  
    DATE(2017,7,1),  
    DATE(2017,7,30)))=0,"Lapse","Not Lapse")

?

[Anonymous]

Thanks for your response. I receive this error: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed

[Greg_Deckler]

Try doing your CALCULATE as a VAR and then your IF as a RETURN using your variable.

VAR m = CALCULATE(.....)

RETURN (IF(m=0...)

Something like that maybe.

[Anonymous]

Thanks, I'll see if I can get that to work. I think I may need to try a different approach due to the way I want to build this out. 

CALCULATE(SUM(LNApps_Facts[Application_Count]), DATESBETWEEN(LNApps_AppDateDim[ActualDate], 
DATE(2017,8,1), 
DATE(2017,8,31) 
))

How can I use this formula but just sum by an ID?

[vcastello]

If you create a Calendar Table(with Dates), and relate it to your "Fact Table" Date, you could ...

IF(

    ISBLANK(
          CALCULATE(
                DISTINCTCOUNT(LNApps_ProviderDim[Provider_Name]),
                FILTER(
                       ALL(Calendar),

                       Calendar[Date] <= MAX(Calendar[Date]) &&
                       Calendar[Date] >= MIN (Calendar[Date])

                       )
              )
        ),
      "Lapse",
      "Not Lapse"
)

Then you can use the same formula for every filter  

[Anonymous]

Thanks vcastello, I don't think I understand the formula, there's no summation in there. I want to avoid creating other tables because that tends to get hairy pretty fast for me. If I could just figure out how to sum the app count by ID and by date I think I will be okay. See below

.

CALCULATE(SUM(LNApps_Facts[Application_Count]), DATESBETWEEN(LNApps_AppDateDim[ActualDate], 
DATE(2017,8,1), 
DATE(2017,8,31) 
))

[v-sihou-msft · Accepted solution]

@Anonymous

If you need to have your calculation group by ID and date, you can add ALLEXCEPT() into your CALCULATE().

=
CALCULATE (
    SUM ( LNApps_Facts[Application_Count] ),
    DATESBETWEEN (
        LNApps_AppDateDim[ActualDate],
        DATE ( 2017, 8, 1 ),
        DATE ( 2017, 8, 31 )
    ),
    ALLEXCEPT ( LNApps_Facts, LNApps_Facts[ID], LNApps_Facts[Date] )
)

Regards,