How to make graph that show from first month until selected month filter.

[agustira97]

Hello,

I try to make a graph that show from the first month of the selected year until selected month filter.
(example: when we select year 2021 and month 5, it will show from january to may)

But as you can see from the pic, when the filters at 2021 05, the graph just show may, not from january until may.
Here's the look of my data:
This is period table

and this is model table

I've already make 2 measure that contain Start and End Period
This measure to always show First Month:

Curr_Min_Month =

VAR _min = MIN('All_Period'[ALL_PERIOD 1.vDateId])

VAR _year = LEFT(_min,4)

RETURN

CALCULATE(

MIN(All_Period[ALL_PERIOD 1.vDateId]),

VALUE(All_Period[ALL_PERIOD 1.vYear]) = VALUE(_year),

VALUE(All_Period[ALL_PERIOD 1.vMonth]) = 1,

ALL(All_Period)

)

This measure to always show Selected Month:

Curr_Month =

VAR _max = MAX('All_Period'[ALL_PERIOD 1.vDateId])

VAR _year = LEFT(_max,4)

RETURN

CALCULATE(

MAX(All_Period[ALL_PERIOD 1.vDateId]),

VALUE(All_Period[ALL_PERIOD 1.vYear]) = VALUE(_year),

VALUE(All_Period[ALL_PERIOD 1.vMonth]) = VALUE(IF(LEN(_max) = 2, RIGHT(_max,2), RIGHT(_max,1))),

ALL(All_Period)

)

This Measure that I put on the Graph:

Measure =

VAR _date = [Curr_Month]

VAR _soy = [Curr_Min_Month]

VAR _calc =

CALCULATE(

SUM('Model'[CalculatedTable1.CountRows]),

'Model'[CalculatedTable1.vDateId] >= _soy &&

'Model'[CalculatedTable1.vDateId] <= _date

)

RETURN

IF(_calc = BLANK(), 0, _calc)

Please help where that i did wrong.
Here's I attached my pbix (Page2)
PBIX Testing 

[agustira97]

Hi, thank you for the answer @FreemanZ 

I've already tried it on my case and It works.

Next Question is, this slicer not just on 1 graph, but also the other graph. It means another graph measure also need to adjust with this isolated slicer right?

Cause I have another chart like gauge chart, but I dont need to sum from the first month of the year, it just need to sum from selected year.
Just to make sure, Thank you.

[FreemanZ · Accepted solution]

hi @agustira97 

tried to simulate your scenario with simplified dataset like:

1) plot a slicer with a column from an isolated table like:

2) plot a visual with month column of your record table and a measure like this:

Measure = 
IF(
    MAX(TableName[Month])<=SELECTEDVALUE(Slicer[MonthSlicer]),
    SUM(TableName[Amt]),
    BLANK()
)

You see, when you choose a different month in the slicer, the chart axis changes accordingly:

The piont is:

the column to feed the slicer needs to be isolated from your record table, otherwise you selecting a data point instead of a range.