Select all records less than the given month

[mallikarjuna_n]

Hi,

I have table where having PERIOD_MMYY as MMYY along with other columns. my entair model is filtering on this PERIOD_MMYY column

if the user select a period PERIOD_MMYY from slicer then my vizualization table should show all the data for the given year and less or equal to given Month.

Is there any way to store our slicer value in a variable so it can be used in filter?

else any soluntion for this?

Example:

PERIOD_MMYY	Col1	Cal2
0123
0223
0323
0423
0523
0623
0723
0823
0923
1023
1123
1223
0124
0224
0324
0424
0524
0624
0724
0824
0924

scenario1: user selected in slicer : 0523

Expected:

PERIOD_MMYY	Col1	Cal2
0123
0223
0323
0423
0523

scenario2: user selected as 0824

Then expected is:

PERIOD_MMYY	Col1	Cal2
0124
0224
0324
0424
0524
0624
0724
0824

FYI .. each month can have multiple records.

Thank you in Advance.

[v-fenling-msft]

Hi, @mallikarjuna_n 

May I ask what your specific data looks like?

Since you didn't give specific data for testing, I assumed some data for testing myself:

My PERIOD_MMYY is a Date type, so I went through Custom Column in Power Query Editor and created a new column to convert PERIOD_MMYY1 to Month+Year to match your data:

Text.PadStart(Text.From(Date.Month([PERIOD_MMYY])), 2, "0") & Text.End(Text.From(Date.Year([PERIOD_MMYY])), 2)

Then New table, created a table for Slicer to filter PERIOD_MMYY:

Table2 =
SELECTCOLUMNS (
    Table1,
    "PERIOD_MMYY", Table1[PERIOD_MMYY],
    "PERIOD_MMYY1", Table1[PERIOD_MMYY1]
)

Then created a Measure, dragged the Measure to the Filters panel set the Measure to 1 to filter the data:

FilterDate =
VAR selectPeriod =
    SELECTEDVALUE ( Table2[PERIOD_MMYY1] )
VAR selectMonth =
    LEFT ( selectPeriod, 2 )
VAR selectYear =
    RIGHT ( selectPeriod, 2 )
VAR CurrentMonth =
    LEFT ( MAX ( Table1[PERIOD_MMYY1] ), 2 )
VAR CurrentYear =
    RIGHT ( MAX ( Table1[PERIOD_MMYY1] ), 2 )
RETURN
    IF ( CurrentYear = selectYear && CurrentMonth <= selectMonth, 1, 0 )

I have attached the pbix file for this example below, hope this helps.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mallikarjunan]

Hi,

Thank you for your reply...

as you requested below is the sample data(not got option to upload) along with this table there are 5 other tables which are joining on different columns but  some tables have book period. In this table the book period is a string not date type.

FILE_ID	SOURCE_ID	FILE_STATUS	BOOK_PERIOD_YY	RECEIVED_DATE	CMNT	CREATED_DATE
27	IN	Complete		14-Jun-24		14-Jun-24
26	PK	Request To Unload	624	10-Jun-24	test	10-Jun-24
25	PK	Reprocess Errors	624	06-Jun-24	testing DB file to check asdf	06-Jun-24
24	PK	Complete	524	13-May-24		13-May-24
23	IN	Complete	524	13-May-24		13-May-24
22	PK	Deleted	524	06-May-24	File was sent with amount different to wire received.  New file received from Alight.	06-May-24
21	IN	Complete	524	03-May-24		03-May-24
20	IN	Complete	424	02-May-24	Load amount from payroll.	02-May-24
19	IN	Deleted	524	01-May-24	Incorrect amount was processed.	01-May-24
18	IN	Complete	424	17-Apr-24		17-Apr-24
17	PK	Complete	424	05-Apr-24	bmdoiejm,dddsfadfasf	05-Apr-24
16	IN	Complete	324	02-Apr-24	asdfsadfasfoiuweo	02-Apr-24
15	IN	Complete	424	01-Apr-24	oieuropihvadsf	01-Apr-24
14	PK	Complete	324	14-Mar-24	Upload of February Direct Bill	14-Mar-24
13	IN	Complete	324	13-Mar-24	Load oiwquriohoadjhv	13-Mar-24
12	IN	Deleted	324	11-Mar-24	received new file	11-Mar-24
11	PK	Deleted	324	05-Mar-24	new file received	05-Mar-24
10	IN	Ready To Process	224	04-Mar-24	test	04-Mar-24
9	IN	Passed Edits	324	01-Mar-24	load	01-Mar-24
8	PK	Complete	224	27-Feb-24	load Jan DB file	27-Feb-24
7	PK	Deleted	224	27-Feb-24	delete	27-Feb-24
6	IN	Complete	224	15-Feb-24	load 2nd source file	15-Feb-24
5	PK	Deleted	224	05-Feb-24	delete	05-Feb-24
4	IN	Complete	124	02-Feb-24	load INernal contr	02-Feb-24
3	IN	Request To Unload	224	01-Feb-24	test	01-Feb-24
2	IN	New File		04-Jan-24		04-Jan-24
1	IN	Passed Edits	124	03-Jan-24	Process Jan amount	03-Jan-24

[v-fenling-msft · Accepted solution]

Hi, @mallikarjuna_n 

What should I do with the null value corresponding to your field BOOK_PERIOD_YY? Are the nulls removed, or are they supposed to occur in what context?

I do not handle null values in the following method.

First you need New Column to convert the field BOOK_PERIOD_YY to the following form:

FormattedPeriod = 
VAR YearPart = RIGHT ( Table1[BOOK_PERIOD_YY], 2 )
VAR MonthPart = MID ( Table1[BOOK_PERIOD_YY], 1, LEN(Table1[BOOK_PERIOD_YY]) - 2 )
RETURN
    IF (
        LEN(MonthPart) < 2 && NOT ISBLANK(MonthPart),
        "0" & MonthPart & YearPart,
        Table1[BOOK_PERIOD_YY]
    )

Then New Table:

Table2 = SELECTCOLUMNS(Table1,"FormattedPeriod",Table1[FormattedPeriod])

Remember not to link the newly created table to other tables:

Then New Measure:

FilterDate = 
VAR selectPeriod = SELECTEDVALUE ( Table2[FormattedPeriod] )
VAR selectMonth = LEFT ( selectPeriod, 2 )
VAR selectYear = RIGHT ( selectPeriod, 2 )
VAR CurrentMonth = LEFT ( MAX ( Table1[FormattedPeriod] ), 2 )
VAR CurrentYear = RIGHT ( MAX ( Table1[FormattedPeriod] ), 2 )
RETURN
    IF (
        ISBLANK(selectPeriod),
        1,
        IF ( VALUE(CurrentYear) = VALUE(selectYear) && VALUE(CurrentMonth) <= VALUE(selectMonth), 1, 0 )
    )

Finally drag Measure to the Filter panel and set Measure to 1 to show only rows with Measure of 1:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mallikarjunan]

@v-fenling-msft 

I want to apply same slicer for all the other pages (5 pages), in this case do I need to do any setting at filter level?

I did synchronization setting for all pages but seems its not applying.

plz suggest me if I need to do any settings.

Best Regards,

Mallikarjuna N

[v-fenling-msft]

Hi, @mallikarjunan 

What specific data do you need to filter?

Let's say the data on the other page is the same as page 1:

You need to set FilterDate to 1 in the Filter panel of the other pages:

Also you need to enable this feature, then select the corresponding page to apply this Slicer too:

This is the official documentation about Slicer, hope it will help you:

Slicers in Power BI - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mallikarjunan]

Thank you somuch @v-fenling-msft it worked.

[v-fenling-msft]

Hi, @mallikarjunan 

It looks like you have found a solution. Could you please mark this helpful post as “Solution”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shravan133]

Try this: FilteredData =
VAR SelectedPeriod = SELECTEDVALUE('YourTable'[PERIOD_MMYY])
VAR SelectedYear = RIGHT(SelectedPeriod, 2)
VAR SelectedMonth = LEFT(SelectedPeriod, 2)
RETURN
CALCULATE(
SUM('YourTable'[Col1]), -- Or use whatever calculation is appropriate for your table
FILTER(
'YourTable',
RIGHT('YourTable'[PERIOD_MMYY], 2) = SelectedYear &&
VALUE(LEFT('YourTable'[PERIOD_MMYY], 2)) <= VALUE(SelectedMonth)
)
)