cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aymane
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:

Aymane_0-1650456425049.png

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:
Aymane_1-1650456968662.png

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

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
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".

vjingzhang_0-1650851911786.png

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. 

vjingzhang_1-1650852211760.png

 

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

View solution in original post

4 REPLIES 4
Aymane
Frequent Visitor

Thank you for the solution @v-jingzhang!!

v-jingzhang
Community Support
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".

vjingzhang_0-1650851911786.png

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. 

vjingzhang_1-1650852211760.png

 

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

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

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors