Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Tableau to PowerBI

Hi Team,

Please help me in converting below Tableau to PowerBI DAX.

1= IF ([Fiscper])= ([Current Fiscpervalue])
THEN
LOOKUP(SUM([Act Inv]),-1)
END

=======================================

2= IF ([Fiscper])=([Current Fiscpervalue])
THEN [6 Inv Start]
ELSE
LOOKUP(
RUNNING_SUM([6 Inv Start])+
RUNNING_SUM(SUM([8 Week Chang R])),-1)
END

========================================

 

Thanks in advance !

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    2 years ago

    Hi, Anonymous 

    try:

    Calculation 1 = 
VAR CurrentFiscperValue = MAX('YourTable'[Fiscper])
RETURN
IF(
    'YourTable'[Fiscper] = CurrentFiscperValue,
    CALCULATE(
        LOOKUPVALUE(
            'YourTable'[Act Inv],
            'YourTable'[Fiscper], CurrentFiscperValue
        ),
        FILTER(
            'YourTable',
            'YourTable'[Fiscper] = CurrentFiscperValue
        )
    )
)

     

    Calculation 2 = 
VAR CurrentFiscperValue = MAX('RUNNING_SUM'[Fiscper])
VAR RunningSumInvStart = 
    CALCULATE(
        RUNNING_SUM([6 Inv Start]),
        FILTER(
            ALL('RUNNING_SUM'),
            'RUNNING_SUM'[Fiscper] <= CurrentFiscperValue
        )
    )
VAR RunningSumWeekChangeR = 
    CALCULATE(
        RUNNING_SUM(SUM('RUNNING_SUM'[8 Week Chang R])),
        FILTER(
            ALL('RUNNING_SUM'),
            'RUNNING_SUM'[Fiscper] <= CurrentFiscperValue
        )
    )
RETURN
IF(
    'RUNNING_SUM'[Fiscper] = CurrentFiscperValue,
    CALCULATE(
        'RUNNING_SUM'[6 Inv Start],
        FILTER(
            'RUNNING_SUM',
            'RUNNING_SUM'[Fiscper] = CurrentFiscperValue
        )
    ),
    CALCULATE(
        LOOKUPVALUE(
            'RUNNING_SUM'[6 Inv Start],
            'RUNNING_SUM'[Fiscper], CurrentFiscperValue - 1
        ),
        FILTER(
            'RUNNING_SUM',
            'RUNNING_SUM'[Fiscper] = CurrentFiscperValue - 1
        )
    ) + RunningSumInvStart + RunningSumWeekChangeR
)

     

    assuming RUNNING_SUM is the table name.

6 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi, Anonymous 

    try:

    Calculation 1 = 
VAR CurrentFiscperValue = MAX('YourTable'[Fiscper])
RETURN
IF(
    'YourTable'[Fiscper] = CurrentFiscperValue,
    CALCULATE(
        LOOKUPVALUE(
            'YourTable'[Act Inv],
            'YourTable'[Fiscper], CurrentFiscperValue
        ),
        FILTER(
            'YourTable',
            'YourTable'[Fiscper] = CurrentFiscperValue
        )
    )
)

     

    Calculation 2 = 
VAR CurrentFiscperValue = MAX('RUNNING_SUM'[Fiscper])
VAR RunningSumInvStart = 
    CALCULATE(
        RUNNING_SUM([6 Inv Start]),
        FILTER(
            ALL('RUNNING_SUM'),
            'RUNNING_SUM'[Fiscper] <= CurrentFiscperValue
        )
    )
VAR RunningSumWeekChangeR = 
    CALCULATE(
        RUNNING_SUM(SUM('RUNNING_SUM'[8 Week Chang R])),
        FILTER(
            ALL('RUNNING_SUM'),
            'RUNNING_SUM'[Fiscper] <= CurrentFiscperValue
        )
    )
RETURN
IF(
    'RUNNING_SUM'[Fiscper] = CurrentFiscperValue,
    CALCULATE(
        'RUNNING_SUM'[6 Inv Start],
        FILTER(
            'RUNNING_SUM',
            'RUNNING_SUM'[Fiscper] = CurrentFiscperValue
        )
    ),
    CALCULATE(
        LOOKUPVALUE(
            'RUNNING_SUM'[6 Inv Start],
            'RUNNING_SUM'[Fiscper], CurrentFiscperValue - 1
        ),
        FILTER(
            'RUNNING_SUM',
            'RUNNING_SUM'[Fiscper] = CurrentFiscperValue - 1
        )
    ) + RunningSumInvStart + RunningSumWeekChangeR
)

     

    assuming RUNNING_SUM is the table name.

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    2 years ago

    hi, your you were getting errors in the first try because column name was mismatched. I see your column name is in uppercase while in the measure I have used sentence case. 

    whatever I have converted your queries to dax. though the results are same. I see your measures are giving 0 or blank values.