Skip to main content
cancel
Showing results for 
Search instead 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

Reply
atibu
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,
                 [AdditionalTestValue2022],
                 IF(
                   SELECTEDVALUE( 'v_DimDate'[Year] ) = 2023,
                    [AdditioanlTestValue2023],
                    __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
tamerj1
Super User
Super User

Hi @atibu 
Please try

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

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @atibu 
Please try

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

atibu
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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.