Rolling target details Power Bi

[Anonymous]

Hi,

Kindly help me the below formula in power Bi, I am new to Power Bi.

[Anonymous]

Thank you so much Tharun, now its working fine

[tharunkumarRTK]

@Anonymous 

Please elaborate your question and provide sample data and expected output details. Read the instructions here please:         How to Get Your Question Answered Quickly               

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!

[Anonymous]

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%),  

[tharunkumarRTK]

@Anonymous 

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

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!

[Anonymous]

HI Tharun,

i have attached my table details, Kindly help me

[tharunkumarRTK · Accepted solution]

@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 + __MonthTotal  

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