cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Create a table with measures by Year to Date and Quarter to Date

I'm trying to recreate the below table in PowerBI:

Fees & Expenses, Income, and Purchase & Sales are all calculated measures, their DAX queries are as follows:

• Fees & Expenses_YTD = CALCULATE(SUMX(Sheet1,Sheet1[MgmtFees]+Sheet1[OtherExpenses]),DATESBETWEEN(Sheet2[Date],[YearStart],[vMax]))
• Fees & Expenses_QTD = CALCULATE(SUMX(Sheet1,Sheet1[MgmtFees]+Sheet1[OtherExpenses]),DATESBETWEEN(Sheet2[Date],[QuarterStart],[vMax]))
• Income_YTD = CALCULATE(SUM(Sheet1[Income]),DATESBETWEEN(Sheet2[Date],[YearStart],[vMax]))
• Income_QTD = CALCULATE(SUM(Sheet1[Income]),DATESBETWEEN(Sheet2[Date],[QuarterStart],[vMax]))
• Purchases & Sales_YTD = CALCULATE(SUMX(Sheet1,Sheet1[Prices]+Sheet1[Sales]),DATESBETWEEN(Sheet2[Date],[YearStart],[vMax]))
• Purchases & Sales_QTD = CALCULATE(SUMX(Sheet1,Sheet1[Prices]+Sheet1[Sales]),DATESBETWEEN(Sheet2[Date],[QuarterStart],[vMax]))[YearStart]: returns the start of the year[QuarterStart]: returns the start of the quarter[vMax]: returns the max date
I've tried to create a matrix, like above, using these values, but it's not possible to put measures in rows and columns, so I have only been able to create separate tables for QTD and YTD like below:

Is there a way to merge them into one table or to put both QTD and YTD measures into one measure?

1 ACCEPTED SOLUTION
Community Support

Hi @Aymane

You can first create a table like below to have three type values in a column. I named it as "Types".

Then create the following measures. Put your original QTD and YTD measures into them.

``QTD = SWITCH(SELECTEDVALUE(Types[Type]), "Fees & Expenses", [Fees & Expenses_QTD], "Income", [Income_QTD], "Purchases & Sales", [Purchases & Sales_QTD])``
``YTD = SWITCH(SELECTEDVALUE(Types[Type]), "Fees & Expenses", [Fees & Expenses_YTD], "Income", [Income_YTD], "Purchases & Sales", [Purchases & Sales_YTD])``

Put 'Types'[Type] column on Rows and put [QTD]&[YTD] measures into Values well. Turn off Row subtotals option in Format pane. You will get the result you want.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

4 REPLIES 4
Frequent Visitor

Thank you for the solution @v-jingzhang!!

Community Support

Hi @Aymane

You can first create a table like below to have three type values in a column. I named it as "Types".

Then create the following measures. Put your original QTD and YTD measures into them.

``QTD = SWITCH(SELECTEDVALUE(Types[Type]), "Fees & Expenses", [Fees & Expenses_QTD], "Income", [Income_QTD], "Purchases & Sales", [Purchases & Sales_QTD])``
``YTD = SWITCH(SELECTEDVALUE(Types[Type]), "Fees & Expenses", [Fees & Expenses_YTD], "Income", [Income_YTD], "Purchases & Sales", [Purchases & Sales_YTD])``

Put 'Types'[Type] column on Rows and put [QTD]&[YTD] measures into Values well. Turn off Row subtotals option in Format pane. You will get the result you want.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Frequent Visitor

Hi @v-jingzhang ,

Thank you again for your solution.

Is there another solution where you don't have to create a new table into the model?

Best Regards,

Aymane Benkhaldoun

Community Support

Hi @Aymane

In my previous sample, I used a matrix visual. So at least a column is needed to play as a row header or a column header. Do you have any existing tables that can provide "Fees & Expenses", "Income", "Purchases & Sales" values or "QTD" & "YTD" values? If so, you can use that column in the matrix instead. It just needs a column to provide values so that the measures can use them to identify different scenarios.

Jing

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors