- Power BI forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Help with formula

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Help with formula

07-22-2024
07:48 AM

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 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-22-2024
04:32 PM

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

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-23-2024
06:53 AM

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

Announcements

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

Learn from experts, get hands-on experience, and win awesome prizes.

Featured Topics

Top Solution Authors

User | Count |
---|---|

110 | |

100 | |

97 | |

88 | |

68 |

Top Kudoed Authors

User | Count |
---|---|

169 | |

133 | |

130 | |

103 | |

95 |