Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
agustira97
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)

agustira97_0-1676543477596.png

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

agustira97_1-1676543692795.png

and this is model table

agustira97_2-1676543734887.png


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 

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @agustira97 


tried to simulate your scenario with simplified dataset like:

FreemanZ_2-1676778015389.png

 

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

FreemanZ_1-1676777668972.png


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:

FreemanZ_5-1676778172894.png

 

FreemanZ_4-1676778150172.png

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. 

View solution in original post

4 REPLIES 4
agustira97
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.

FreemanZ
Super User
Super User

hi @agustira97 


tried to simulate your scenario with simplified dataset like:

FreemanZ_2-1676778015389.png

 

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

FreemanZ_1-1676777668972.png


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:

FreemanZ_5-1676778172894.png

 

FreemanZ_4-1676778150172.png

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. 

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.

Vidya1882_0-1711364271836.png

Regards,

Vidya

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.