topic Re: Calculate only first 3 years of data. in DAX Commands and Tips

Calculate only first 3 years of data.

ruthe23 — Wed, 15 May 2024 00:57:46 GMT

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!

Re: Calculate only first 3 years of data.

Jihwan_Kim — Wed, 15 May 2024 03:07:30 GMT

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 ) ) )

Re: Calculate only first 3 years of data.

ruthe23 — Thu, 16 May 2024 02:02:35 GMT

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!

Re: Calculate only first 3 years of data.

ruthe23 — Thu, 16 May 2024 02:11:47 GMT

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.

Re: Calculate only first 3 years of data.

Jihwan_Kim — Thu, 16 May 2024 02:35:35 GMT

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?