cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbuser Member
Member

Limit to N months displayed on a Sales by Month bar chart

Trying to build a simple bar chart say Sales by months.

  1. ideally only display a fixed number of months (Say 12 months only even if the data contains more months data)
  2. ideally only display a fixed number of months from CURRENT month (no hard coding)

Any advice on the best practice to implement this?

5 REPLIES 5
Super User
Super User

Re: Limit to N months displayed on a Sales by Month bar chart

You should be able to use a technique similar to:

 

DaysPast = DATEDIFF([Date],TODAY(),DAY)

DATEDIFF will also take "MONTH" in the last parameter. Then you can just set the filter to something like DaysPast > 30 days or 12 months for example.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

pbuser Member
Member

Re: Limit to N months displayed on a Sales by Month bar chart

Thanks for that. It does work.

 

The only problem is that it hard coded to be today() which is current day/month. In reality, users want to be able to do from a selected / specified month (EG from a slicer). In the other words, can we do past N months from current SELECTED month instead of current month? 

sdjensen Senior Member
Senior Member

Re: Limit to N months displayed on a Sales by Month bar chart

There is a feature request that will enable users to make an offset to time intelligence - please vote if you think it will satisfy your needs.

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13805892-time-intelligence-base-t...

 

/sdjensen
Moderator Eric_Zhang
Moderator

Re: Limit to N months displayed on a Sales by Month bar chart

@pbuser

 

According to your description, it seems that you're looking for a date range slicer. There's already one official customized The Timeline Slicer.

You can download the visualization and sample from here.

 

Capture.PNG

 

goutamgm92 Frequent Visitor
Frequent Visitor

Re: Limit to N months displayed on a Sales by Month bar chart

I am tackling a similar scenario but for some reason, I am having some problem and I am not able to figure out the solution

 

In a table, I am having a column with the date. I have only 6 months of Date and data in the table.

 

When I pull the column to a slicer, it shows the first date and last date correctly where as when I pull the date to a table, it lists all the months. Is this a bug?

 

I want to calculate the number of months I have selected, but the measure always ends up showing 12 instead of 6.

 

The measure I am using is 

 

CountOfMonths = DATEDIFF(FIRSTDATE(Table1[SampleDate].[Date]),
    LASTDATE(Table1[SampleDate].[Date]),MONTH)

 

image.png

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 40 members 1,148 guests
Please welcome our newest community members: