Forum Discussion
Help with formula
Hi guys,
I am new to DAX, i am trying to get this following formula to work, it validades, but doesnt display amounts. Can someone help?
Thanks,
=VAR SelectedView = IF(HASONEFILTER(View[View]), VALUES(View[View]))
VAR ActualsPeriod =
SWITCH(
SelectedView,
"Q0F", CALCULATE(SUM(Transactions[Amount]),
Transactions[Period] >= "1" && Transactions[Period] <= "13"),
"Actuals + Q1F", CALCULATE(SUM(Transactions[Amount]),
Transactions[Period] >= "1" && Transactions[Period] <= "3"),
"Actuals + Q2F", CALCULATE(SUM(Transactions[Amount]),
Transactions[Period] >= "1" && Transactions[Period] <= "6"),
"Actuals + Q3F", CALCULATE(SUM(Transactions[Amount]),
Transactions[Period] >= "1" && Transactions[Period] <= "9"),
BLANK()
)
VAR ForecastPeriod =
SWITCH(
SelectedView,
"Actuals + Q1F", CALCULATE(SUM(Transactions[Amount]),
Transactions[Period] >= "4" && Transactions[Period] <= "13"),
"Actuals + Q2F", CALCULATE(SUM(Transactions[Amount]),
Transactions[Period] >= "7" && Transactions[Period] <= "13"),
"Actuals + Q3F", CALCULATE(SUM(Transactions[Amount]),
Transactions[Period] >= "10" && Transactions[Period] <= "13"),
BLANK()
)
RETURN
ActualsPeriod + ForecastPeriod
2 Replies
Anonymous
Please supply the data this calculation is using, it's hard to debug it without the data.
Also, what result(s) do you expect? Need to know that in order to figure out why it isn't working as expected.
regards
Phil
Thanks for taking the time and looking into it. I really appreciate your help on this.
I've made a change to the formula, and while it partially works, it doesn't fully achieve my intended outcome.
I am using the data model to pull data from both actuals and forecasts. We have four forecast cycles throughout the year, referred to as Q0F (covering periods 1 to 13), and then adjusted quarterly as Q1F (periods 4-13), Q2F (periods 7-13), and Q3F (periods 9-13).
My goal with the formula is to display the actuals as they come in, and for the remainder of the year, the 13-column file should display the forecast data at that point in time.
For example, if I select "Actuals + Q2F" in the view (which is a table linked to the transactions table), I want the amounts to be displayed as follows: periods 1-7 for actuals and periods 8-13 for forecast. What I attempted was to display actuals plus forecast, while excluding the forecast for periods where actuals already exist.
Here is a screenshot of what I was able to achieve. It works, but when I select a different view, the totals do not match. The updated formula is included below.
I Also, included the link to the sample data.
What I noticed is that in the first row of the formula, it only works properly when I refer to one of the possible selection e.g "Actuals && Q1F", but when I select another view the calculation doesnt work. I'm not sure if it's possible to have multiple criteria in that field so I can use the same formula.
=VAR SelectedView = IF(HASONEFILTER(View[View]), VALUES(View[View]),"Actuals && Q1F")
VAR ActualsPeriod =
SWITCH(
TRUE(),
SelectedView = "Q0F", BLANK(),
SelectedView = "Actuals", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 13),
SelectedView = "Actuals && Q1F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 3),
SelectedView = "Q1F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 3),
SelectedView = "Actuals && Q2F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 6),
SelectedView = "Q2F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 6),
SelectedView = "Actuals && Q3F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 9),
SelectedView = "Q3F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 9),
BLANK()
)
VAR ForecastPeriod =
SWITCH(
TRUE(),
SelectedView = "Q0F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 13),
SelectedView = "Actuals && Q1F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 4 && Transactions[Period] <= 13),
SelectedView = "Q1F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 4 && Transactions[Period] <= 13),
SelectedView = "Actuals && Q2F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 7 && Transactions[Period] <= 13),
SelectedView = "Q2F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 7 && Transactions[Period] <= 13),
SelectedView = "Actuals && Q3F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 10 && Transactions[Period] <= 13),
SelectedView = "Q3F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 10 && Transactions[Period] <= 13),
BLANK()
)
VAR ForecastZeroPeriod =
SWITCH(
TRUE(),
SelectedView = "Actuals && Q1F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 3, Transactions[View] = "Q1F"),
SelectedView = "Q1F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 3),
SelectedView = "Actuals && Q2F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 7, Transactions[View] = "Q2F"),
SelectedView = "Q2F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 6),
SelectedView = "Actuals && Q3F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 9, Transactions[View] = "Q3F"),
SelectedView = "Q3F", CALCULATE(SUM(Transactions[Amount]), Transactions[Period] >= 1 && Transactions[Period] <= 9),
BLANK()
)
RETURN
ActualsPeriod+ForecastPeriod-ForecastZeroPeriod
