I'm trying to visualize incoming costs vs outstanding invoices. I managed to partially get what I want but the one thing that I can't figure out is to make an predicted revenue based on previous year.
Basically I have these tables Date Date | Year | Month |Day
Date to Pay | Amount to Pay | Year
Date of Revenue | Amount of Revenue | Year
The Date table is an ordinary table with dates up to 2020. The invoice table has dates from both paid invoices and to invoices to pay in future. For example 5th of april 2018.
The revenue table only has dates from the past.
I managed to make a graph dat displays all invoices and history revenues. But now I want to make prediction of the revenue based on previous year. I managed to calculate a measure using the following code:
Revenue_LY = CALCULATE([SumRev]; FILTER(ALL('Revenue_table'); 'Revenue'[Year]=MAX('Revenue'[Year]) - 1); SAMEPERIODLASTYEAR('Revenue'[Date of Revenue]))
How ever that function stops at the present. I want to make the revenue show of previous year against all outstanding invoices.
PS: I'm totally new to DAX & PowerBI. Study material is welcome too