Forum Discussion

tommuirwebb's avatar
tommuirwebb
Frequent Visitor
6 years ago
Solved

Month slicer to filter YTD

I have 2 table visualisations showing data for 2018 and one slicer which contains the month of the year.

 

I'd like this month slicer to control both table visualisations. However I want one table to show data for the month selected in the slicer and I want the other table to show data YTD (so filter the data where the date is less than or equal to the month selected in the slicer).

 

The first table is easy to acheive, it's the YTD table I am struggling with. Any suggestions would be appreciated.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    tommuirwebb - It would be preferable to have a Date table, because it enables you to use Time Intelligence functions.

    However, if you only have month names to join the tables, you could:

    1. Add a month key to the month table, e.g. 1 for Jan, 2 for Feb, etc.
    2. Add a YTD Calculation like this - this assumes your table only has a single year of data:
    Value YTD = 
var _selected_month = SELECTEDVALUE(MonthTable[MonthKey])
return CALCULATE(
    SUM(YourTable[YourValue],
    MonthTable[MonthKey] <= _selected_month 
)
    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    tommuirwebb - It would be preferable to have a Date table, because it enables you to use Time Intelligence functions.

    However, if you only have month names to join the tables, you could:

    1. Add a month key to the month table, e.g. 1 for Jan, 2 for Feb, etc.
    2. Add a YTD Calculation like this - this assumes your table only has a single year of data:
    Value YTD = 
var _selected_month = SELECTEDVALUE(MonthTable[MonthKey])
return CALCULATE(
    SUM(YourTable[YourValue],
    MonthTable[MonthKey] <= _selected_month 
)
    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.
    • tommuirwebb's avatar
      tommuirwebb
      Frequent Visitor
      6 years ago

      Thanks Anonymous. I was able to get the desired results using a sepearate calendar table (with no relationships to any other tables) and using this table for the slicer. Then using your Value YTD measure I was able to sum the values appropriately.

       

      Actuals YTD = (
      VAR _Curr_month = SELECTEDVALUE('Calendar'[MonthNumber])
      return
      calculate(sum(Actuals[Actual_Amount]),Actuals[MonthNumber] <= _Curr_month ))
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Can you create a month table like this and then use the dates based on month selection to control same

    MonthTable = 
var FullCalendar = ADDCOLUMNS(CALENDAR("2016/1/1","2017/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
return 
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601