Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
arsene49
Helper I
Helper I

DAX Measure/Column Help

Hi All, Need a help to create DAX measure/column.

There are 2 tables, below is the sample data
1.Year-Factor
which has Factor for each year

Year    Factor
2021    1.035
2022    1.035
2023    1.035
None   1


2.Fact
there will be muliple products, multiple years and price

Product Year Price
A        2020    10
B        2021    15
C        2022    20


Requirement -
there will be Year slicer in the page from Year-Factor table and user will be choosing the year

 

ex. when they select 2022
if we look at the fact table:
expected output -
for product A which belongs to year 2020
output= price * 2021factor * 2022factor (10*1.04*1.04)
for product B which belongs to year 2021
output= price * 2022factor

final measure output - sum of Product A output + Product B output + Product C output

 

ex. when they select 2023
expected output - 
for product A which belongs to year 2020
output= price * 2021factor * 2022factor * 2023factor(10*1.04*1.04*1.04)
for product B which belongs to year 2021
output= price * 2022factor *2023factor

final measure output - sum of Product A output + Product B output + Product C output

 

ex. when they select None, 
for Product A , measure output is just Price

 

expected output when each year selected:

   filter selection in slicer
ProductYearPrice2021 selected2022 selected     2023 selected     

    None Selected

A20201010.3510.71225   11.08717875    10
B2021151515.525   16.068375    15
C2022202020   20.7    20
1 ACCEPTED SOLUTION

12 REPLIES 12
devanshi
Helper V
Helper V

TotalCost=

VAR selected=SELECTEDVALUE(Year-Factor[Year])

VAR summed = SUMMARIZED('Fact',[Product],[Year],"PY")

VAR year='Year-Factor'[Year]  

      RETURN 

           'Fact'[Price]*(year+1,[Factor],"yf")*(yf+1,[Factor])

RETURN

    sumx(year,PY)

lbendlin
Super User
Super User

there will be Year slicer in the page from Year-Factor table and user will be choosing the year

For that to work the Year-Factor table must be disconnected.

 

Your expected output is unclear for products that fall outside the limits. Please indicate the expected outcome for all products and Year-Filter selections.

 

lbendlin_0-1683598739925.png

 

 

 

@lbendlin Thanks for the reply. i have added a table at the end of the question which shows the expected output when each value of the Year-Factor slicer is selected. let me know if you need any further information.

@lbendlin i have added expected outcome for all products and Year-Filter selections. did you get chance to look?

Adjusted =
VAR yf =
    SELECTEDVALUE ( 'Year-Factor'[Year] )
VAR a =
    SUMMARIZE (
        'Fact',
        [Product],
        [Year],
        "sm",
            VAR y = [Year]
            RETURN
                SUM ( 'Fact'[Price] )
                    * IF (
                        yf = "None",
                        1,
                        COALESCE (
                            CALCULATE (
                                PRODUCTX ( 'Year-Factor', [Factor] ),
                                'Year-Factor'[Year] <= yf,
                                'Year-Factor'[Year] > y
                            ),
                            1
                        )
                    )
    )
RETURN
    SUMX ( a, [sm] )

See attached

 

Thanks @lbendlin 

just last help.

 

we have altred the Year-Factor table by adding an extra column "Factor2"

Year    Factor    Factor2
2021    1.035    0.98
2022    1.035    0.98
2023    1.035    0.98
None    1          1

 

now we need to consider Factor2 in the calculation when Year-Factor.Year < Fact.Year
for rest your given forumula is correct.

 

ex. for Product D - which belongs to year 2023
when they select 2021
expected output -
output= price * 2022 factor2 * 2023 factor2 (10*0.98*0.98)

 

when they select 2022
output= price * 2023 factor2 (10*0.98)

 

expected output. (you just need to add the scenario for the bold one in the current formula)

 

   filter selection in slicer
ProductYearPrice2021 selected2022 selected    2023 selected    None
A20201010.3510.7122511.0871787510
B2021151515.52516.06837515
C20222019.62020.720
D2023109.6049.81010

You can change the PRODUCTX calculation accordingly. 

@lbendlin tried playing around with that function, couldn't get the exact output. can you please help?

Have you replaced factor with factor2 in the formula?

 

it is not clear to me  what your expected outcome is. Please clearly indicate the expected outcome for all scenarios.

 

 

Thanks for respoding @lbendlin . let me explain the scenarios - 

 

there are 3 scenarios based on the year filter selction we make and year in Fact where each product belongs.
1.Year Selected silcer (from Year-Factor) > Fact Table Year (for each product) = here we need to consider factor to multiply price
2.Year Selected (Year-Factor) < Fact Table Year = here we need to consider factor2 to multiply price
3.Year Selected (Year-Factor) = Fact Table Year and Year Selected (Year-Factor)="None" = here we need to consider 1 to multiply price

 

ex. when we select 2021 in the filter
if we look at the fact table:
expected output -
for product A which belongs to year 2020 (Here Year Selected silcer (from Year-Factor) > Fact Table Year (for each product))
output= price * 2021factor = (10*1.04)
for product B which belongs to year 2021 (Here Year Selected (Year-Factor) = Fact Table Year)
output= price * 1 = (15*1)
for product C which belongs to year 2022 (Here Year Selected (Year-Factor) < Fact Table Year)
output= price * 2021factor2 = (20*0.98)
for product D which belongs to year 2023 (Here Year Selected (Year-Factor) < Fact Table Year)
output= price * 2022factor2 *2021factor2= (10*0.98*0.98)

 

ex. when we select 2022 in the filter
if we look at the fact table:
expected output -
for product A which belongs to year 2020
output= price * 2021factor*2022factor = (10*1.04*1.04)
for product B which belongs to year 2021
output= price * 2021factor = (15*1.04)
for product C which belongs to year 2022
output= price * 1 = (20*1)
for product D which belongs to year 2023
output= price *2022factor2= (10*0.98)

 

final measure output = sum of Product A output + Product B output + Product C output + Product D output

 

Year-Factor table-

Year      Factor      Factor2
2021      1.035       0.98
2022      1.035       0.98
2023      1.035       0.98
None     1              1

 

expected output - 

   filter selection in slicer
ProductYearPrice2021 selected   2022 selected   2023 selected   None
A20201010.35   10.71225   11.08717875   10
B20211515   15.525   16.068375   15
C20222019.6   20   20.7   20
D2023109.604   9.8   10   10

 

 

@lbendlin  i have added expected outcome for all scenarios. did you get chance to look?

see attached

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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