cancel
Showing results for
Did you mean:
Member

## DAX filter for the current month in date dimension is needed

Hello, I created a measure in DAX (see below) to return the total anual budget ignoring filters:

`Anual Budget = CALCULATE(SUM(FactBudget[BudgetValue]),ALL(DimDate[Date]))`

The budget Fact contains budget values in the same granularity as the Date Dimension (DimDate). Thus, there is one value on each day.

FactBudget (Table)

```Date            | BudgetValue

-----------------------------

2018-01-01 | \$100

2018-01-02 | \$ 200

....```

Date Dimension DimDate

```Date

------

2018-01-01

2018-01-02

...```

I would like to do the same to claculate the total budget of the current month only what will be needed instead of

`ALL(DimDate[Date])`

?

What I need is something like Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),GETCURRENTMONTH(DimDate[Date])) where GETCURRENTMONTH does not exist.

What I tried but it does nto return correct results was:

`Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),(YEAR( DimDate[Date] ) = YEAR ( TODAY()) && MONTH( DimDate[Date] ) = MONTH (TODAY ())))`

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: DAX filter for the current month in date dimension is needed

I solved the issue in the meanwhile. Here is the solution that worked for me and what I was doing wrong:

`Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),DATESBETWEEN(FactBudget[Date],DATE(YEAR(TODAY()), MONTH(TODAY()), 1),EOMONTH(today(),0)))`

So the issue was that I was using the Date Dimension which is a rolling Date dimension to show dates only till today so I had to get the date from the original budget table.

2 REPLIES 2
Frequent Visitor

## Re: DAX filter for the current month in date dimension is needed

i think what we can do is create a new column that gives the month number from the dates column by editing the query.

Then create measure as below
Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),FactBudget[Month]=MONTH(TODAY()))

where Month is the newly created column name

Member

## Re: DAX filter for the current month in date dimension is needed

I solved the issue in the meanwhile. Here is the solution that worked for me and what I was doing wrong:

`Monthly Budget = CALCULATE(SUM(FactBudget[BudgetValue]),DATESBETWEEN(FactBudget[Date],DATE(YEAR(TODAY()), MONTH(TODAY()), 1),EOMONTH(today(),0)))`

So the issue was that I was using the Date Dimension which is a rolling Date dimension to show dates only till today so I had to get the date from the original budget table.

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!