Forum Discussion

dmarsh's avatar
dmarsh
Frequent Visitor
7 years ago
Solved

Prior Year Revenue with Direct Query (no time intelligence)

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...
Need Help
  • dax's avatar
    dax
    7 years ago

    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.

dax's avatar
dax
Community Support
7 years ago

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's avatar
dmarsh
Frequent Visitor
7 years ago

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!