cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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)

Here's I attached my pbix (Page2)
PBIX Testing

1 ACCEPTED SOLUTION
Super User

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.

2 REPLIES 2
Frequent Visitor

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.

Super User

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.

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors