Filtering the months in a line chart to display cumulative values up to the selected month

[hruv01]

Hello, I'm having trouble coming up with the following task. I have a calendar table created in Power BI, and it is connected to another two tables, the first contains the number of working days in each month and the second is a fact table. I need to create a line chart, where the months' names are displayed on the x-axis and the values are calculated by dividing the number of items from the fact table by the number of working days from this table. However, I also want to enable a filter on the page based on the months, so that when the user selects March, for example, the cumulative values up to that chosen month will be displayed, resulting in the graph showing January, February, and March. How can I have connected tables and simultaneously filter cumulative values by month without filtering only the selected one?

[Anonymous · Accepted solution]

Hi  @hruv01 ,

I created some data:

Month_Worknumber：

Items:

Relationships in tables:

Here are the steps you can follow：

1. Create measure.

Value =
var _items=
SUMX(
    FILTER(ALLSELECTED('items'),
    YEAR('items'[Date])=YEAR(MAX('items'[Date]))&&
    MONTH('items'[Date])=MONTH(MAX('items'[Date]))),[Projectnumber])
var _work=
SUMX(
    FILTER(ALLSELECTED('Month_Worknumber'),
    YEAR('Month_Worknumber'[Date])=YEAR(MAX('Month_Worknumber'[Date]))&&
    MONTH('Month_Worknumber'[Date])=MONTH(MAX('Month_Worknumber'[Date]))),[Worknumber])
return
_items / _work

Flag =
var _year=SELECTEDVALUE('calendar table'[Year])
var _month=SELECTEDVALUE('calendar table'[Month])
return
IF(
    YEAR(MAX('Month_Worknumber'[Date]))=_year &&MONTH(MAX('Month_Worknumber'[Date]))<=_month,1,0)

Measure =
SUMX(
    FILTER(ALLSELECTED(Month_Worknumber),
    YEAR('Month_Worknumber'[Date])=YEAR(MAX('Month_Worknumber'[Date]))&&
    MONTH('Month_Worknumber'[Date])<=MONTH(MAX('Month_Worknumber'[Date]))),[Value])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

the first contains the number of working days in each month 

Eliminate that table and add a "working day" flag in your calendar table.