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 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.

 

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.

2 Replies

  • 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!