Prior Year Revenue with Direct Query (no time intelligence)

[dmarsh]

My dataset has the following columns: 'Revenue', 'Year', 'Contract_Number'. I need to calculate PY Revenue, but I can't use time itelligence due to DirectQuery and a large dataset.

My problem: If a Contract doesn't have Revenue in a given year, it will not show up in the dataset. So if I want to show Prior Year Revenue in a table and there is Revenue in 2016, but not 2017 for a given Contract_Number, Prior Year Revenue will be blank in 2017. This issue is illustrated below. 

I think the root of the problem is that my "CY" variable can only calculate the Current Year based on the contract having revenue. I tried solving this problem by filtering "ALL" 'Contract_Numbers' on my "CY" calculation (see below), but it didn't seem to help.

Thank you for any help you can provide. Please let me know if further clarification is needed.

[dax · Accepted solution]

Hi dmarsh,

According to your description, it seems that you want to show previous year amount in table(although there is no data for that year), right?

My sample:

id          year      revenue system

1	2015	100
1	2016	200
1	2017	300
2	2015	20
2	2016	200
2	2017	10
2	2018	30
3	2015	50
3	2016	100
4	2015	500
4	2016	300
4	2017	100

I create another table year by below measure, you don't need to create relationship between above two tables

year = VALUES(test[year])

The create two measures like below

current year =
CALCULATE (
    SUM ( test[revenue system] ),
    FILTER (
        ALL ( test ),
        test[id] = MIN ( test[id] )
            && test[year] = MIN ( 'year'[year] )
    )
)

previous =
CALCULATE (
    SUM ( test[revenue system] ),
    FILTER (
        ALL ( test ),
        test[id] = MIN ( test[id] )
            && test[year]
                = MIN ( 'year'[year] ) - 1
    )
)

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dmarsh]

Zoe,

Thank you for your reply! I did not know that you can create a new table without a relationship and that your calculations would understand the context. I'm going to have to make some tweaks to your calculation for my dataset, as the MIN function will not work on the "ID" field (because "Contract_Number" in my data is a Text field) but I think I should be able to make your methodology work. I'll mark this as an Accepted Solution and then post back with any new findings. 

Thanks again!