Forum Discussion

okusai3000's avatar
okusai3000
Helper IV
7 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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)

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

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/434008

 

Should 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