Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Dynamic Measures

Hello All,
I am new to PowerBI. 
I want to create a measure that can give me data based on the current month's data.

For example: If I have the Current Month i.e. November with value = 1000

So I want to have a Projection value based on the calculation which is  = ((November Value /Days elapsed in November)*Total Number of Days in November)

If we take current date as 3rd November then Formula would be - (1000/3)*30 = 10000
It should be a dynamic formula for every month with 28,30 and 31 days with respect to each month.

 

Kindly help me out.

 

Regards,
AJP

Need Help

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello ryan_mayu 
      I have given a sample set as asked by you.
      I want to automate the Projection using DAX. Here, the Formula for November Projection is : 

      (Current Month Revenue)/(No. of Days elapsed in the current month) * (Total No. of days in the current Months)

      For our case, it would be - 3000/3*30 = 30000

       

      DateCompanyRevenue
      31-07-2022Company_11000
      31-08-2022Company_12000
      30-09-2022Company_12000
      31-10-2022Company_12500
      03-11-2022Company_13000
      November ProjectionCompany_130000

       

      So, what will be the DAX for the Projection of the current month?

       

      Regards,
      Aditya

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        3 years ago

        Anonymous 

        maybe you can try this to create a measure

        Measure = 
VAR _date=maxx(FILTER('Table',year('Table'[Date])=year(today())&&month('Table'[Date])=month(today())),'Table'[Date])
VAR _value=maxx(FILTER('Table','Table'[Date]=_date),'Table'[Revenue])
return _value/day(_date)*day(EOMONTH(_date,0))