Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !
Solved! Go to Solution.
Hi, @Raks
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.
Proud to be a Super User!
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.
Proud to be a Super User!
Hi, @Raks
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.
Proud to be a Super User!
can you send a demo file?
Proud to be a Super User!
Here is the link
https://drive.google.com/file/d/1nnp8ajrCfy4yDvrTlS69beGkje0TZM9g/view?usp=drive_link
======================
6 Inv Start= IF ([Fiscper])= ([Current Fiscpervalue])
THEN
LOOKUP(SUM([Act Inv]),-1)
END
=========================
11 Inv Start= IF ([Fiscper])=([Current Fiscpervalue])
THEN [6 Inv Start]
ELSE
LOOKUP(
RUNNING_SUM([6 Inv Start])+
RUNNING_SUM(SUM([8 Week Chang R])),-1)
END
===============================
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
115 | |
106 | |
96 | |
81 | |
72 |