topic Re: How to make graph that show from first month until selected month filter. in DAX Commands and Tips

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

agustira97 — Thu, 16 Feb 2023 10:43:49 GMT

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

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

FreemanZ — Sun, 19 Feb 2023 03:44:05 GMT

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.

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

agustira97 — Mon, 20 Feb 2023 05:04:39 GMT

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.

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

Vidya1882 — Mon, 25 Mar 2024 11:01:06 GMT

Hi FreemanZ
This is my question also, i used your measure in my scenerio .but from my side this measure is not working ,attached screenshot for reference. please give breif answer as soon as possible.

Regards,

Vidya

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

FreemanZ — Tue, 26 Mar 2024 08:35:11 GMT

hi agustira97 try first leaving out the second [Open Total] in your code?