Billing and Collection Time Intelligence Issue

[Joseph_Hchaime]

Hi,

I have a bills table and an allocations table hosted on SQL. 

I created a date dimension table and linked it to both tables (Screenshot attached). 

In the Bills Table I have a column called Net Billed

In the Allocations table I have a column called Net Collected

I also need to have a relationship between Bills Table and Allocations Table based on Bill ID (One Bill issued could have multiple payments).

Whenever I add them both with the Year hieararchy of the date dimension table, the data is wrong.

 I want to show for every year how much was billed and how much was collected.  

Please advise. 

[amitchandak · Accepted solution]

@Joseph_Hchaime , Bill table, and allocation table should not join. whatever allocation need from the bill table, add those columns using merge in power query

Merge Tables (Power Query) : https://www.youtube.com/watch?v=zNrmbagO0Oo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=16

Also, add a year to the date table. Mark it as date table and then you should be able to compare data using year from date table

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)

also, check

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...