How to dynamic flag Jan till July when now is Aug ?

[admin11]

Hi All

I have posted below question :-

https://community.powerbi.com/t5/Desktop/How-to-hide-22-07-column/td-p/2671064

Can some one share with me how to dynamic flag YrMth ?

when now is 29 July 2022 , i will like to filter Jan till June , not include July.

when now is 1 Aug 2022 , i will like to filter Jan till July , not include Aug.

Need to use some thing month_n = 1

Paul

[tamerj1]

Hi @admin11 

you can create a filter measure 

IF ( Month ( MAX ( 'Date'[Date] ) ) < MONTH ( TODAY ( ) ), 1 )

Place it in the filter pane of the matrix visual, select "Is" and inser the value "1" then apply the filter. 

[admin11]

@tamerj1 

I have try your expression , when click on apply , it will filter off all date . 

Hope you can help to take a look where go wrong ?

Paul

[tamerj1 · Accepted solution]

@admin11 
It should be a measure not a calculated column. 

[admin11]

@tamerj1 

Apprecaite your help, Thank you very much. Since today 1 Aug , i don;t have any data in yet , so i am not able to test the expression it is working fine.

By the way i just like to know that , if i need to dynamic filter last 2 month ago data off :-

FLAG_DYNAMIC_DATE1 = IF ( Month ( MAX ( 'Date'[Date] ) ) < MONTH ( TODAY ( ) -0), 1 )

FLAG_DYNAMIC_DATE1 = IF ( Month ( MAX ( 'Date'[Date] ) ) < MONTH ( TODAY ( ) -1), 1 )

FLAG_DYNAMIC_DATE1 = IF ( Month ( MAX ( 'Date'[Date] ) ) < MONTH ( TODAY ( ) -2), 1 )

Meaning , assume now is Aug , i want to filter off Aug and July.

I have try above expression , it does not work.

Paul

[tamerj1]

@admin11 

While doing such calculations please pay attention to the year as well. If today is for example Jan 15 then the previous month do not equal 1 -1 = 0 or the month before 1 - 2 = -1. In this case you need to have a year-month sequential number column in your date table. Which is a dense ranking of the year-month number. Then we can comfortably use this column to retun the n previous months. 

[admin11]

@tamerj1 

Thank you for supper fast response. I she'll wait for Aug data come in . From you reply you mention that I need to make use YrMth field . Can you pls share the expression with me .

[tamerj1]

@admin11 

I will soon as I return to office 

[amitchandak]

@admin11 , Create a new column in the date table

if([Date] >= (eomonth([Date], -1*Month([Date])) + 1) && [Date] <= eomonth([Date],-1) ,1,0)

[admin11]

@amitchandak 

i Have try your expression.

when i activated , it will filter off all date.

Hope you can give me advise.

Paul