Date Filtering - Start of Current Year to Today

[rodjarman]

I'm relatively new to Power BI having used it for the last 3 months.

I am trying to produce a budget-based report comparing budget to sales.

In the budget table, I have budgets for each month for 2016. I would like to filter the report so it only displays budget amounts from the start of the year up until the current month.

The budget_table has fields for Year, MonthNo, MonthName, BudgetAmount, and Date - with Date being the date of the end of the month.

I'd really appreciate some help on setting up approriate filter.

thanks,  Rod.

[v-qiuyu-msft · Accepted solution]

Hi Rod,

According to your description, you want to display report data based on the date range between start date of year 2016 and current month, right?

In your scenario, as the budget table contains data for 2016, you can create a new column to dataset to tell which row is before current month with the DAX expression like below:

Before_current_month = IF( [Month]<=MONTH(Now()),"before_current_month","")

Then use this new column “Before_current_month” in filters. For more information, please refer to this article: Finding the Latest Date in Power BI Desktop.

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

[ikkew]

If you have any question, please feel free to ask.

@v-qiuyu-msft I'm a bit late but I'll be taking you up on this offer.

I have a seperate table containing a column called 'Month' (values: 1-12). Yet I can't seem to use your function since my Month is grayed out. Instead, I'll have to rewrite it to this:

Before_current_month = IF( VALUES([Month])<=MONTH(Now()),"before_current_month","")

I don't seem to be able to drag this measure to the filter page- or report section.

Any ideas? Would your solution work with the VALUES() function?

Edit: Made a measure instead of a column before :S
My question still remains since I get months shown after the current month

[rodjarman]

Thanks QiuYun.

I also found this suggested solution which worked:

https://sqldusty.com/2015/09/14/creating-time-calculations-in-power-bi/