Calculate no filtering by Month

[12Bowers12]

Good moring, everyone,

I have a measure [Premium] works under different contexts, except for date.

For example, if I want to group by Month, it always returns the a constant Total amount for each month, without any filtering.

Appreciate your help.

Dennis

Premium =

VAR CurrentYear = YEAR(MAX(DateIndex[AS_OF_DATE]))

VAR LatestDate = MAX(DateIndex[AS_OF_DATE])

RETURN

   CALCULATE (

       SUM(PolicyData[NET_PREMIUM]),

       DateDim[Year] = CurrentYear,

       DateDim[Date] <= LatestDate,

       PolicyData[NET_PREMIUM] <> 0 )

[parry2k]

@12Bowers12 what is dateindex table? Why are you doing filter on year and latest date? If you have a direct relationship with your date table and transaction table, you can simply show sum using month from date dimension. Seems like I'm missing something here.

[12Bowers12]

Thank you, Mr. Prray, I think your point is right.
What I really needs is to aggregate the premium on monthly basis for the current calendar year 2020 as of the evaluation date such as 06/30/2020.
DateDim table covers many years including such as future calendar year such as 2021, 2022, etc.
To deal this issue, I created a table with one row and one column only showing the [As_Of_Date] 06/30/2020.

I tested if use the filter argument as below, it works but very slow.

Any improvements or solutions?
Dennis

Premium_Filter =

VAR CurrentYear = YEAR(MAX(DateIndex[AS_OF_DATE]))

VAR LatestDate = MAX(DateIndex[AS_OF_DATE])

RETURN

   CALCULATE (

       SUM(PolicyData[NET_PREMIUM]),

       DateDim[Year] = CurrentYear,

       DateDim[Date] <= LatestDate,

       FILTER( PolicyData, CALCULATE(SUM(PolicyData[NET_PREMIUM])) <> 0 ))

[v-deddai1-msft]

Hi @12Bowers12 ,

Why you need FILTER( PolicyData, CALCULATE(SUM(PolicyData[NET_PREMIUM])) <> 0 ) in your formula?

Best Regards,

Dedmon Dai

[12Bowers12]

Thank you, Dedmon,

Without this filtering argument, the measure could not group the premium on monthly basis. But I don't know why.

Regards,

Dennis

[v-deddai1-msft]

Hi @12Bowers12 ,

I suggest you create the following measure without creating new table for one cell:

Premium_Filter = 
VAR _parameter = ...

VAR CurrentYear = YEAR(_parameter)

VAR LatestDate = _parameter

RETURN

   CALCULATE (

       SUM(PolicyData[NET_PREMIUM]),

       DateDim[Year] = CurrentYear,

       DateDim[Date] <= LatestDate,

      SUM(PolicyData[NET_PREMIUM])) <> 0 )

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

Best Regards,

Dedmon Dai

[12Bowers12]

Thank you, Dedmon, 

I tried but it didn't return any value. I will test it again later.

Dennis