Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Values for Current Month

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 mea...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    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.

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

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).