Struggling with filters when projecting into future.

[Jakester6153]

I have this report I created that has a bunch of premium data by month and year and a bunch of other class type variables such as product and program. My report has a few measures and also takes in some user input in the form of a what if parameter. Because my report needs to project premium into the future, I created a date table with the dates in my dataset as well as dates in the future so I can project premium for the rest of the year. One of the measures simply sums the premium up like so, Total_written_prem = sum(table[DWP]). Then I have my projected premium measure that uses this total premium measure in order to determine projected premium amounts:

ProjectedPremium = 

VAR PriorDWP = CALCULATE(
    [Total_Written_Prem],
    DATEADD('Date Table'[Date], -1, YEAR)
)
VAR FutureDWP = CALCULATE(
    [Total_Written_Prem],
    DATEADD('Date Table'[Date], +1, YEAR)
)

VAR currentDWP = calculate([Total_Written_Prem])


Return if(isblank(currentDWP), PriorDWP*'YoYGrowth%'[Parameter Value], if(isblank(PriorDWP), blank(), currentDWP))

yoyGrowth% is the what if parameter.

Doing this works as my dataset has premium only through April 2024 and my visual displays the premium for Jan-April 2024 and then the projected amounts for May-Dec 2024. The only problem is as soon as I click on any of my filters, so changing product to GL, my visual changes to just displaying the premium for Jan-April and all the projections disappear.

Does anyone know why this may be happening? My first thought was that is must have something to do with my what if parameter. Like the filter is causing that what if parameter to break and so my measure is returning blanks. I have no idea how I would fix that though.

[rajendraongole1]

Hi @Jakester6153 -Create a below measure for better handling of the filtered context.When you filter by product, program, or any other category, the context of your calculations changes, which can result in blanks if the data for the previous or future year doesn't exist for the filtered context.

Try the below measure:

ProjectedPremium =
VAR PriorDWP =
CALCULATE(
[Total_Written_Prem],
DATEADD('Date Table'[Date], -1, YEAR)
)

VAR FutureDWP =
CALCULATE(
[Total_Written_Prem],
DATEADD('Date Table'[Date], +1, YEAR)
)

VAR CurrentDWP =
[Total_Written_Prem]

VAR YoYGrowth = 'YoYGrowth%'[Parameter Value]

RETURN
IF(
ISBLANK(CurrentDWP),
IF(
ISBLANK(PriorDWP),
BLANK(),
PriorDWP * YoYGrowth
),
CurrentDWP
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Jakester6153]

Ok wow. I just stummbled along a solution. I changed the cross filter direction from both to single. Now when I change the filter, the months after April still appear. Do you have any idea why this is? I swear I remember reading somewhere to set the corss filter direction to both when creating a date table. 

[Jakester6153]

Hello, I tried this but the same thing occurs. I am really struggling to figure out what is the issue. Your reccomendation gave me an idea and so I did return if(isblank(priorDWP), 1, priorDWP). As soon as I change the filter to anything else i get 1 for all the months after April. Not sure why the fix above then didn't work. Also there should not be any blanks based on my dataset. For instance, I have GL data back to 2021 with no blanks and when I filter to GL it returns 1 still.  

I can share a link to my project if that would help. I think I just need an email to send it to as I don't believe you can just post a link here.