Forum Discussion
Rolling target details Power Bi
Anonymous
Below is the sample data that I have prepared:
Today is Sept 1st 2024. So, the Target value as per your formula and as per your fiscal calendar is (Q1 + July + Aug + Sept)
Formula:
Target (On This Month) 2 = Var __CurrentDay = TODAY() Var __CurrentMonth = MONTH(EOMONTH( __CurrentDay,-3)) Var __CurrentQuarter = INT( DIVIDE(__CurrentMonth,3)) Var __CurrentYear = YEAR(__CurrentDay) Var __NoOfMonths = 3 - INT(MOD(__CurrentMonth,3)) Var __FirstMonthInQuarter = __CurrentMonth - (__NoOfMonths - 1) Var __QuarerTotal = CALCULATE(SUM('Target 2'[Annl Target]), INT( RIGHT('Target 2'[Qtr]) ) < __CurrentQuarter && 'Target 2'[Year] = __CurrentYear) Var __MonthTotal = CALCULATE(SUM('Target 2'[Annl Target]), 'Target 2'[MonthNum] >= __FirstMonthInQuarter && 'Target 2'[MonthNum] <= __CurrentMonth && 'Target 2'[Year] = __CurrentYear) RETURN __QuarerTotal + __MonthTotalI am attaching the PBIX file.
P.S. Please provide complete details in your post, along with sample data and expected result. So that community members can help you better.
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
HI Tharun,
Thank you for replying,
I have two table, one Sales target, second table is Actual, So now i need target as on today same like Actual also, here below target i have attached formula.
For Exm: Now We are there in Q2 3 month So i need Q1+M1(30%)+M2(35%)+M3(35%) if i am there in Oct then Q1+Q2+M1(30%),
Super UserAnonymous
Based on my understanding, below formula will work for your requirement:
Target (On This Month) =
Var __CurrentMonth = MONTH(TODAY())
Var __Quarter = INT( DIVIDE(__CurrentMonth,3))
Var __NoOfMonths = INT(MOD(__CurrentMonth,3))
Var __QuarerTotal =
SWITCH(
TRUE(),
__Quarter = 1, 0,
__Quarter = 2, Target[Q1(22%)],
__Quarter = 3, Target[Q1(22%)] + Target[Q2(26%)],
__Quarter = 4, Target[Q1(22%)] + Target[Q2(26%)] + Target[Q3(26%)]
)
Var __MonthTotal =
SWITCH(
TRUE(),
__NoOfMonths = 0, Target[Month 1(30%)] + Target[Month 2(35%)] + Target[Month 3(35%)],
__NoOfMonths = 1, Target[Month 1(30%)],
__NoOfMonths = 2, Target[Month 1(30%)] + Target[Month 2(35%)]
)
RETURN __QuarerTotal + __MonthTotal
I am attaching the PBIX for your reference:
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
|
HI Tharun,
i have attached my table details, Kindly help me
- tharunkumarRTK
Anonymous
Below is the sample data that I have prepared:
Today is Sept 1st 2024. So, the Target value as per your formula and as per your fiscal calendar is (Q1 + July + Aug + Sept)
Formula:
Target (On This Month) 2 = Var __CurrentDay = TODAY() Var __CurrentMonth = MONTH(EOMONTH( __CurrentDay,-3)) Var __CurrentQuarter = INT( DIVIDE(__CurrentMonth,3)) Var __CurrentYear = YEAR(__CurrentDay) Var __NoOfMonths = 3 - INT(MOD(__CurrentMonth,3)) Var __FirstMonthInQuarter = __CurrentMonth - (__NoOfMonths - 1) Var __QuarerTotal = CALCULATE(SUM('Target 2'[Annl Target]), INT( RIGHT('Target 2'[Qtr]) ) < __CurrentQuarter && 'Target 2'[Year] = __CurrentYear) Var __MonthTotal = CALCULATE(SUM('Target 2'[Annl Target]), 'Target 2'[MonthNum] >= __FirstMonthInQuarter && 'Target 2'[MonthNum] <= __CurrentMonth && 'Target 2'[Year] = __CurrentYear) RETURN __QuarerTotal + __MonthTotalI am attaching the PBIX file.
P.S. Please provide complete details in your post, along with sample data and expected result. So that community members can help you better.
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!