CODEWITHSUNDEEP
powerbidaxpowerquery
Laura_777
Laura_777

Reputation: 55

Combine multiple measures in a single matrix by different date

I need some help with report view in Power BI. I need to create matrix which would show dates in columns and values of my created measures each month. The problem is that my data is complicated because there is no mutual date column, each measure is counted by different date, for example measure "New Participants" is counted by "Signing date" column, measure "Exits" is counted by "Close date" column and so on. And if I choose to put one of those dates columns in matrix I do not get good result, because adding to matrix Signing date would give me wrong numbers of Exits and vice versa.

In excel I had just manually made table with dates written and values pasted from pivot. I need something similar in Power BI.

enter image description here

What I get in Power BI are just two matrices which have different date in column:

enter image description here

Upvotes: 0

Views: 763

Answers (1)

Pieter
Pieter

Reputation: 724

You need a calendar table. Then you just join each of your two fact tables to it. New Participants by Signing Date and Exits by Close Date. Then you add a MonthYear Column, and you're sorted. you can use CALENDARAUTO() to generate a list of dates in all the date ranges in your fact tables, so your final calendar table would be something like

ADDCOLUMNS(CALENDARAUTO(),
"Year", YEAR([Date]),
"Month",MONTH([Date]),
"MonthName",FORMAT([Date],"MMM")
)

then you just join to those from your two fact tables, and you can do running totals etc. you can create a Calendar table using CALENDARAUTO() and then add whatever columns you like using the UI too. Likely easier that way.

Upvotes: 0

Related Questions