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

[Aymane]

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?

[Aymane]

Thank you for the solution @v-jingzhang!!

[v-jingzhang · Accepted solution]

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.

[Aymane]

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

[v-jingzhang]

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