Forum Discussion
Bar Chart show data from current date each month
Hello everyone,
I need guidance for what I currently thinking, is it possible to create a Bar Chart that will show data from certain dates or filtered dates in each month? Well, let's say I want to see how much data that will show up on the 1st date of each month. Like the data on 1st Jan, 1st Feb, 1st March. And from there I want to do a comparison from each date on each month.
This is my current bar chart that I already create on the PowerBI, but I'm trying to accomplish the idea that has already been described before. Thank you.
Hi Anonymous
If I am understanding your requirement, you should be able to take the "Day" from the Date Hierarchy and add that as a Slicer so that you can select the Day of each Month and compare only the Day that you want (i.e. first, second, third, fourth, etc., etc)...
Hope this helps 🙂
9 Replies
- amitchandak
Super User
Anonymous , Create a measure like
=
var _max = maxx(allselected('Date'), 'Date'[Date])
var _min = maxx(allselected('Date'), 'Date'[Date])
return
calculate(sum(Table[Value]), filter('date', 'Date'[Date] >=_min && 'Date'[Date] <=_max && 'Date'[Date] = eomonth(min('Date'[Date]),-1)+1 ))or
=
var _max = maxx(allselected('Date'), 'Date'[Date])
var _min = maxx(allselected('Date'), 'Date'[Date])
return
calculate(sum(Table[Value]), filter('date', 'Date'[Date] = eomonth(min('Date'[Date]),-1)+1 ))or
calculate(firstnonblankvalue'Date'[Date], sum(Table[Value]))
Hi amitchandak, thank you for your reply.
I try your 1st formula, but when I put that to the Bar Chart, everything goes blank. Same with the 3rd formula.
I try the 2nd formula, which only shows 1 date data only and I can't change it to other dates by using a filter.
- TheoC
Community Champion
Hi Anonymous
If I am understanding your requirement, you should be able to take the "Day" from the Date Hierarchy and add that as a Slicer so that you can select the Day of each Month and compare only the Day that you want (i.e. first, second, third, fourth, etc., etc)...
Hope this helps 🙂
Hi TheoC, thank you for your reply.
This is exactly what I need, I didn't know I could use Day from hierarchy, still need to learn more about PBI. Anyways, if your way is using a filter, how to make it automatically following the latest date of the current month in my data? I'm still searching for how to make the formula for it.
But totally, your solution makes me easier to go to the next step.- TheoC
Anonymous yeah, Power BI is pretty powerful and is far more simple to use than we often think.
My sincere apologies, however, I am just trying to get a better understanding of what you are wanting to achieve. Are you aiming to return the most current day such as "TodaysDay = DAY ( TODAY () )" or are you aiming to return the most recent Date in all of your Date column such as "LastDate = LASTDATE ( 'Table'[Date] )" or possibly the Day of the most recent date such as "Most Recent LastDate = DAY ( LASTDATE ('Table'[Date] ) )"?
If the above is not what you were hoping for, please let me know and I will do my best to help out 🙂
