Calculate only first 3 years of data.

[ruthe23]

I can't seem to get a way to evaluate and calculate my columns.  Let me explain.  I have Volume Data by Model that I started tracking in 2018.  Here's an few example of data. 

Model #	Vol18	Vol19	Vol20	Vol21	Vol22	Vol23	Vol24	First 3Years
A	-	-	39,288	124,716	27,696	22,728
B	-	42,354	29,116	41,620	4,932	384
C	-	-			23,758	18,474	1,974
D	-	-	25,228	30,482	22,104	12,440	12,422
E	-	-	40,832	65,132	0

what I need is to find the year that has the first volume data and then add the next 2 years after that.  That's what I want in the last column.  As you can see they don't necessarly start in the same year.  They may just be starting this year or the year prior and not always have 3 years to calculate or they may have 3 years of data where one of the years is a zero.  I can seem to get it started with evaluating looking for a zero if zero then move on and look at the next column.  Once I have a nonzero then I could put that into a variable but then I am not sure how to get the next 2.  or where to go from there.   Thanks!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Sales total: = 
SUM( sales_fact[sales] )

WINDOW function (DAX) - DAX | Microsoft Learn

Sales total first 3 years: = 
VAR _t =
    WINDOW (
        1,
        ABS,
        3,
        ABS,
        FILTER ( ALL ( year_dimension[year] ), [Sales total:] <> BLANK () ),
        ORDERBY ( year_dimension[year], ASC )
    )
RETURN
    IF (
        HASONEVALUE ( model_dimension[model_#] ),
        CALCULATE ( [Sales total:], KEEPFILTERS ( _t ) )
    )

[ruthe23 · Accepted solution]

Now I need one step further.  Now that I have 

Sales total first 3 years:

How can I use that measure to calculate with?  I have a query with the same model #'s and a forecast value.  I want to get percent of forcast using this sales total.  It doesn't appear as an option when I create a new measure. 

[Jihwan_Kim]

Hi,

Thank you for your message, but I am not sure if I understood your question correctly.
Could you please provide how the expected outcome looks like in the sample?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Sales total: = 
SUM( sales_fact[sales] )

WINDOW function (DAX) - DAX | Microsoft Learn

Sales total first 3 years: = 
VAR _t =
    WINDOW (
        1,
        ABS,
        3,
        ABS,
        FILTER ( ALL ( year_dimension[year] ), [Sales total:] <> BLANK () ),
        ORDERBY ( year_dimension[year], ASC )
    )
RETURN
    IF (
        HASONEVALUE ( model_dimension[model_#] ),
        CALCULATE ( [Sales total:], KEEPFILTERS ( _t ) )
    )

[ruthe23]

Perfect.  I was totally making things much harder for myself.  I am really new to Power BI and this really made me think of some new ways of doing things.  thanks!