cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## How to add two numbers based on selected years

Hi everyone,

currently i`m developing a tabular model with a bunch of DAX logic in it. One of the requirements is that for the years 2022 & 2023 some additional quantities must be added to the base measure.

For the year 2022 it would be 26 pcs per month

For the year 2023 it would be 27,5 pcs per month

If i only select the single years everything seems to be fine, the adjusted measure is defined like this:

VAR __Market =
CALCULATE(
SUM( 'v_factMarket'[Count] ),
'v_DimMarketCompany'[Group] = "Market"
)

RETURN
IF(
HASONEVALUE( 'v_DimMarketActivity'[ChartType] ),
IF(
SELECTEDVALUE( 'v_DimDate'[Year] ) = 2022,
IF(
SELECTEDVALUE( 'v_DimDate'[Year] ) = 2023,
__Market
)
)
)

Now the users also can mix several previous years with the years that have the addition. F. ex. year 2021 & 2022. In that case it sums the values for both years but for 2022 without the addition of 26 pcs. F.ex. the total value for January 2021 is 3609 and for January 2022 without the addition is 3876. The adjusted value for 2022 with the additional pieces for January is  3902. Now if i select the two years i would expect that it uses for January 2022 the value 3902 instead of  3876 and creates the sum 7511 pcs. Instead it still takes the value for Jan 2022 3876 and creates the sum for both Jan 2021 and Jan 2022 = 7485. The question would be how can i make sure that it uses the adjusted when selecting the adjusted years and a older year without the added pieces.

Thank you in advance!

1 ACCEPTED SOLUTION
Super User

Hi @atibu

``````CountMeasure =
VAR AddCount2022 = 26
VAR AddCount2023 = 27.5
RETURN
SUMX (
SUMMARIZE ( 'v_DimDate', 'v_DimDate'[Year], 'v_DimDate'[Month] ),
VAR __Market =
CALCULATE (
SUM ( 'v_factMarket'[Count] ),
'v_DimMarketCompany'[Group] = "Market"
)
RETURN
IF (
HASONEVALUE ( 'v_DimMarketActivity'[ChartType] ),
SWITCH ( 'v_DimDate'[Year], 2022, AddCount2022, 2023, AddCount2023 ) + __Market
)
)``````

However, I don't understand the purpose of HASONEVALUE ( 'v_DimMarketActivity'[ChartType] )

2 REPLIES 2
Super User

Hi @atibu

``````CountMeasure =
VAR AddCount2022 = 26
VAR AddCount2023 = 27.5
RETURN
SUMX (
SUMMARIZE ( 'v_DimDate', 'v_DimDate'[Year], 'v_DimDate'[Month] ),
VAR __Market =
CALCULATE (
SUM ( 'v_factMarket'[Count] ),
'v_DimMarketCompany'[Group] = "Market"
)
RETURN
IF (
HASONEVALUE ( 'v_DimMarketActivity'[ChartType] ),
SWITCH ( 'v_DimDate'[Year], 2022, AddCount2022, 2023, AddCount2023 ) + __Market
)
)``````

However, I don't understand the purpose of HASONEVALUE ( 'v_DimMarketActivity'[ChartType] )

Frequent Visitor

Hi @tamerj1,

thanks! that solved my issue - really appreciate your help.

However, I don't understand the purpose of HASONEVALUE ( 'v_DimMarketActivity'[ChartType] )

This is just a precondition required by the business but doesnt interfere with anything else.

Thanks again!