topic Re: Income Statement that shows Current Month and YTD columns in DAX Commands and Tips

Income Statement that shows Current Month and YTD columns

H_insight — Sun, 25 Jun 2023 08:31:21 GMT

I am aiming to create the following table or matrix in Power BI and would greatly appreciate your assistance. Depending on the chosen Year and Month, I would like to show In Month / YTS & Full Year values. The column headers, need to show Revenue vs Expenses dynamically and display the corresponding values. Below is the required output layout.

Attached sample model, where Scenario's means ( BU = Budget, AC = Actual, FC = Forcast).

Sample File

Thank you.

Re: Income Statement that shows Current Month and YTD columns

v-yueyunzh-msft — Tue, 27 Jun 2023 01:40:20 GMT

Hi, H_insight

Thanks for your sample pbix file first!

According to your description, you want to create a custom matrix in Power BI Desktop.

For this need , you need to create a dimension table like this:(the index column is used for sorting the Title column)

And then you can put the fields on the visual and we can get this :

For your needs, I don't know your specific calculation logic, you can use the MAX() function to get the column title, and then use the IF() or SWITCH() function to return the corresponding value according to different situations to achieve your needs.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

Re: Income Statement that shows Current Month and YTD columns

H_insight — Tue, 27 Jun 2023 09:18:50 GMT

Thank you v-yueyunzh-msft 👍

Out of interest, what about the values and variance? do I need a special Dax to achieve it?

Re: Income Statement that shows Current Month and YTD columns

Ferrol40 — Mon, 30 Sep 2024 16:35:48 GMT

Can you write 1 of the switch measures ? Also Can you try to achieve this with a calculation column to reduce the Dax measures?

Re: Income Statement that shows Current Month and YTD columns

PBIdashboards — Mon, 22 Jun 2026 04:23:48 GMT

The SWITCH + dimension table approach is the standard pattern for this layout. One thing to add for the multi-scenario version (BU/AC/FC): you need one SWITCH measure per scenario per time period, so for 3 scenarios × 3 time periods = 9 measures minimum. Add variance columns and it doubles.

Pattern for the YTD with scenario filter:

YTD Actual =
CALCULATE(
SUM(FactTable[Amount]),
FactTable[Scenario] = "AC",
DATESYTD('Date'[Date])
)

Replicate for BU and FC, then add variance measures between them.

For Finance teams where this matrix needs to stay flexible (stakeholders switching between In Month / QTD / YTD themselves), Flexa Tables on AppSource handles period switching as built-in buttons no additional measures needed