Forum Discussion
Line Chart until selected month
Hello everyone,
I have made a supplyers report where I'm showing how high has been the total purchase until the month selected. On my first version, I did the running total more "manually" in a way that I had to select month per month to get the running total.
On this version, I created a running total with a quick measure so my idea was to being able to select the desire month in an easier an visual way:
The problem is that now I get just those dots instead of all the values until the selected month.
One option was to disable the link between the chart and the filter selection, but this "solution" just shows the whole year, while my intention is just to show until the selected month.
as you can see here, for "march" it shows the whole year
Do you have any idea of what should I do?
Thank you so much!
Regards from spain!
Hi okusai3000
finally, add the month column from the new created table to a slcier, select an item in this slicer, the line chart only show values untill the selected month.
You only need one slicer, the column added to the slicer is the month column from your new created table, not your original table.
Measure 2 = SELECTEDVALUE(Table1[month])
This show the item when you select a item in the slicer, Table1 is the new created table.
Measure 3 = IF(MAX(your original table[month])<=[Measure 2],1,0)
8 Replies
You need to write your measure for your line chart such that you get the MAX of the Month in your slicer and then you would get ALL of your table and then filter it down accordingly.
You could use something like my Time Intelligence the Hard Way to get the job done. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Should be some variation of:
Gross Margin YTD1 = VAR __Month = MONTH(MAX(Sales[DateKey])) VAR __Day = DAY(MAX(Sales[DateKey])) VAR __Year = YEAR(MAX(Sales[DateKey])) VAR __Today = DATE(__Year,__Month,__Day) VAR __YearStart = DATE(__Year,1,1) VAR __tmpTable = FILTER(ALLSELECTED(Sales),Sales[DateKey]>=__YearStart && Sales[DateKey]<=__Today) RETURN SUMX(FILTER(__tmpTable,MONTH([DateKey])<=__Month),[Gross Margin])
But, sample source data would help greatly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi okusai3000
Assume your running total is correct, to get all the values until the selected month,
first create a new table with a month column (note that don't create relationship between this new table and your data table)
then create a measure
Measure 2 = SELECTEDVALUE(Table1[month])
next create another measure, and add this measure to the visual level filer of the line chart, set 'show items when value is 1)
Measure 3 = IF(MAX([month])<=[Measure 2],1,0)
finally, add the month column from the new created table to a slcier, select an item in this slicer, the line chart only show values untill the selected month.
This is limited for the axis doesn't show other months except the months before selected month.
Best Regards
Maggie
Hi Maggie,
I cannot understand the thing about the filter. I mean, from what I understand you, in that way I would end up having 2 filters?
Thanks!
EDIT: I get something like this:
Hi okusai3000
finally, add the month column from the new created table to a slcier, select an item in this slicer, the line chart only show values untill the selected month.
You only need one slicer, the column added to the slicer is the month column from your new created table, not your original table.
Measure 2 = SELECTEDVALUE(Table1[month])
This show the item when you select a item in the slicer, Table1 is the new created table.
Measure 3 = IF(MAX(your original table[month])<=[Measure 2],1,0)
