Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need Help: Extrapolate the latest closed months numbers to remaining months of year in PBI

Hi PBI Experts, I am looking for your expert advise on extrapolating usage to remaining months of the year in PBI. The requirement is to have February closed month's actual to remaining months. E.g...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2) We can create a date table and a measure. 

    DateTable = CALENDAR(DATE(2024,1,1),DATE(2024,12,31)) 
    Measure 3 = 
var a=MAXX(FILTER(ALLSELECTED('Table'),[type] IN VALUES('Table'[type])),[Date])
var b=CALCULATE(SUM('Table'[actual]),EOMONTH('Table'[Date],0)=EOMONTH(a,0))
var c=CALCULATE(SUM('Table'[actual]),FILTER('Table',EOMONTH([Date],0)=EOMONTH(MAX(DateTable[Date]),0)))
RETURN IF(EOMONTH(MAX(DateTable[Date]),0)>=EOMONTH(a,0),b,c)

    (3) Then the result is as follows.

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a date table and a measure. 

DateTable = CALENDAR(DATE(2024,1,1),DATE(2024,12,31)) 
Measure 3 = 
var a=MAXX(FILTER(ALLSELECTED('Table'),[type] IN VALUES('Table'[type])),[Date])
var b=CALCULATE(SUM('Table'[actual]),EOMONTH('Table'[Date],0)=EOMONTH(a,0))
var c=CALCULATE(SUM('Table'[actual]),FILTER('Table',EOMONTH([Date],0)=EOMONTH(MAX(DateTable[Date]),0)))
RETURN IF(EOMONTH(MAX(DateTable[Date]),0)>=EOMONTH(a,0),b,c)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you so much Anonymous !!! 🕺

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi PBI Experts,

     

    I have connected a folder where monthly actuals will be loaded by respective team and connected to the dashboard. Now the challenge is:

    1) How can I bring months where I don't have actual but can show the latest closed month's actual

    2) For example - January 2024 has 100 actuals February 2024 has 150 actuals. I need to replicate/bring 150 (of February closed) actuals to remaining months

     

    Would it possible to help me out?