Create union report in Power BI

[sonuojha1]

Hi All,

I have a requirement to create a union report just like OBIEE as below. For this, I have 4 measures. Type is hardcoded string.

below is a measure formula.

measure1 = CALCULATE(SUM('Fact - Pay Costing'[CREDIT_AMOUNT]),FILTER('Fact - Pay Costing','Fact - Pay Costing'[CLASSIFICATION_ID]=67),FILTER('Fact - Pay Costing','Fact - Pay Costing'[EFFECTIVE_DATE]<=MAX('Dim - Date'[Date])))

 similarly, i have other 3 measures.

Kindly Note, I don't want to merge/Union tables by using power query.

Expected

Year	Month	Type	Amount
2018	Jan	Earning	100
2018	Feb	Deduction	58
2018	Jan	Expense	32
2018	Jan	Pay	59
2018	Feb	Earning	89
2018	Feb	Expense	67
2018	Feb	Pay	62

OBIEE Union report

1.  Can we achive it without creating any additional table? If yes, how?
2.  By using calculated table:- If we use calculated table and store data as Month level, then how would be proceed with Time filters like year/month from Time Dimension table. As it has month as lowest level and Time Dim has day as lowest.

3. Any alternate option?

Regards,

Sonu

[smpa01]

@sonuojha1  Can we achive it without creating any additional table?

can you try this and see if it works

neeMeasure=[measure1]+[measure2]+[measure3]+[measure4]

tried with some simple measures

m1 = IF(MAX('Table'[index])=1,1000)
m2 = IF(MAX('Table'[index])=2,2000)
m3 = IF(MAX('Table'[index])=3,3000)

[sonuojha1]

Thanks for reply,

However, we have Year/Month data for other measures as well, hence we can't simply add them.

Regards,

Sonu