Values for Current Month

[Anonymous]

I am trying to create a DAX measure that returns the sales budget for the current month. I want it to be optomized so I don't have to use filters on the visual or have to go in and change the DAX measure each time a new month occurs.  

The problem is my table containg the budget information, doesn't have a fantastic date set up. I currently have an excel table that has 4 columns on it: "Date, Budget, S/O (Sales or Orders), and Product Segment"

However the way the budget is categorized does not have specific days attached to it. It only goes by month and year, but in excel, and with the way that Power Bi works, I had to format it somehow with MMM/DD/YYYY. Here's what my excel sheet looks like. 

I wanted it to be formmated by month and year, but again as you can see in the first row, I had to manipulate it with a MMM/DD/YYYY, so I just put down the 1st day for each of the months. (ex: 2/1/25 = Feb-25 etc.)

I'm strugglign to write a DAX formula that can work around this issue. This is the current DAX I have for this measure:

Sales Budget = 
CALCULATE(
SUM('Sales&OrdersBudgets'[Budget]),
FILTER('Sales&OrdersBudgets', 'Sales&OrdersBudgets'[S/O] = "S"),
FILTER('Sales&OrdersBudgets', 'Sales&OrdersBudgets'[Date] = MONTH(TODAY())))

But because it isn't the 1st, the TODAY() function obviously won't work. 

Is there a DAX measure that could help solve this? Or is there something I can do to change the format of the dates in my excel file to match what I am looking for?

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (many to one and single) from the Date column of your Fact table to the date column of the Calendar Table.  Write these measures

Measure = sum('sales&orderbudgets'[Bedget])

This month's budget = calculate(measure],'sales&orderbudgets'[s/o]="s",datesbetween(calendar[date],eomonth(today(),-1)+1),eomonth(today(),0)))

Hope this helps.

[Anonymous]

Hi @Anonymous ,

Make sure that ''Sales&OrdersBudgets''[Date] is a date type:

And then create a calendar date:

Create a relationship between date:

Then use relative date to get the current month budget:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Bibiano_Geraldo]

Hi @Anonymous ,

Make sure that the date column was set to date type format in power query and try the bellow updated DAX:

Sales Budget = 
CALCULATE(
    SUM('Sales&OrdersBudgets'[Budget]),
    'Sales&OrdersBudgets'[S/O] = "S",
    YEAR('Sales&OrdersBudgets'[Date]) = YEAR(TODAY()),
    MONTH('Sales&OrdersBudgets'[Date]) = MONTH(TODAY())
)

[Anonymous]

My date column is set to a date type. I tried the DAX you provided but it is giving me the full years sales budget, when I only want it to give me the current months budget. Here's a screenshot of the DAX measure you created in a visual format (on the left) and what I want the visual to look like without having to filter the visual (on the right).