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 ...
Need Help
ryan_mayu's avatar
ryan_mayu
Super User
3 years ago

could you pls provide some sample data?

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))
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello Ashish_Mathur and ryan_mayu ,
    Thank you so much for your time and effort. Unfortunately, I am not able to populate the data using the formula given by you guys.
    I have curated the DAX as per my tables, but when I am using the measure in any chart it gives blank values. 
    Do you have any clue why that might be happening?
    Kindly help.

     

    Regards,
    AJP

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      3 years ago

      pls share your pbix file