Forum Discussion

KrisT's avatar
KrisT
Frequent Visitor
9 years ago

Rolling Yearly Target

Hi All,

 

I'm still fairly new to PowerBI, so please bear with me.

 

For a financial dashboard I am aiming use a Gague to display the amount of work invoiced so far in this financial year against a rolling target that changes every day depending on targets per quarter. The maximum value of the gague is the financial year target.

 

For this example i'll have a yearly target of £1,000,000, and the financial year starts on 01/06/2016

 

This would be easy if we had static targets per quarter, which uses the forumula: Rolling Yearly Target = 1000000*YEARFRAC("06/01/2016",today(),3)

 

However, we have a per quarter target of Q1 = 100,000. Q2 = 200,000. Q3 = 300,000. Q4 = 400,000. These quarter totals are defined as Measure totals from a budgetting spreadsheet which has different Sector targets per quarter (Retail, Construction, etc.) e.g. Q1 Total = SUM('201617 Budget'[Q1])

 

I'm wondering if it's possible to have a Measure which will update every day to show where we should be so far in the year against how much work has been invoiced so far.

 

Apologies if I haven't adequatly explained. I'm happy to expand further if needed. Thank you in advance.

Need Help

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    KrisT

     

    I'm not quite clear about your table structure. I assume you already build the relationship between the date table and Budget table. Since you store Budget in column like [Q1], you may create a calculated column and use SWITCH() to assign different quarter budget based on current quarter name. 

     

    =SWITCH(Table[Quarter Name],
"Q1",RELATED('201617 Budget'[Q1]),
"Q2",RELATED('201617 Budget'[Q2]),
"Q3",RELATED('201617 Budget'[Q3]),
"Q4",RELATED('201617 Budget'[Q4]))

    If I don't understand your scenario correctly, please share your sample data and expected result.

     

    Regards,