Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Replacing Excel Columns with DAX

I have a following excel table. Adjustment Date, Value, TriggerPoint, and Index % are original columns. I was trying to compute  "Triggered", "Baseline" and "Movement" columns using Dax in Power BI,  logics for those columns in Excel are in Column F, H, and J respectively.

 

A

BCDEFGHIJ

Adjustment Date

ValueTriggerPointIndex %TriggeredTriggered FormulaBaselineBaseline FormulaMovementMovement Formula
9/1/20211.91410%115.70%  1.61.6 is given   
11/1/20212.28110%134.10%Y=IF(ABS(I3) > C3, "Y", "N")2.281=IF(E3="Y", B3, G2)42.56%=B3/G2-1
3/1/20222.31110%135.60%N=IF(ABS(I4) > C4, "Y", "N")2.281=IF(E4="Y", B4, G3)1.32%=B4/G3-1
6/1/20222.30410%135.20%N=IF(ABS(I5) > C5, "Y", "N")2.281=IF(E5="Y", B5, G4)1.01%=B5/G4-1

 

I spent almost entire day but couldn't figure it out. Can someone help me out? Any help will be appreciated!

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 

     

    Based on your infomation, I create a table.

     

    After importing the data into Power BI, create a calculated column called Index to sort the data.

     

     

    Index = 
RANKX(
    ALL('Table'), 
    'Table'[Adjustment Date], 
    , 
    ASC, 
    Dense
)

     

    Next, create three measures as follows

     

    Triggered Formula = 
VAR _Movement = 
    IF(
        ISBLANK(SUM('Table'[Movement])),
        BLANK(),
        CALCULATE(SUM('Table'[Movement]), 'Table'[Index])
    )
VAR _TriggerPoint = 
    CALCULATE(SUM('Table'[TriggerPoint]),'Table'[Index])
RETURN
    IF(
        ISBLANK(_Movement),
        BLANK(),
        IF(_Movement > _TriggerPoint, "Y", "N")
    )

     

    Baseline Formula = 
VAR _Triggered = 
    IF(
        ISBLANK(CONCATENATEX('Table', 'Table'[Triggered], ", ")),
        BLANK(),
        CALCULATE(CONCATENATEX('Table', 'Table'[Triggered], ", "), 'Table'[Index])
    )
VAR _Baseline = 
    CALCULATE(SUM('Table'[Baseline]),  'Table'[Index])

VAR _Value = CALCULATE(SUM('Table'[Value]),'Table'[Index])
RETURN
    IF(
        ISBLANK(_Triggered),
        "1.6",
        IF(_Triggered ="Y", _Value, _Baseline)
    )

     

    Movement Formula =
VAR _Baseline =
    CALCULATE (
        SUM ( 'Table'[Baseline] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = MAX ( 'Table'[Index] ) - 1 )
    )
VAR _Value =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Index] )
RETURN
    IF ( ISBLANK ( _Baseline ), BLANK (), ( _Value / _Baseline ) - 1 )

     

    Here is my preview:

     

     

    How to Get Your Question Answered Quickly 

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.