filter table by year and weekyear

[noliverte]

Hello,

I'd like to create simple filters using a dax measure... I usually use calculated fonction...

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

But I just have dates and text... without other measures like sales,....

I have 2 tables

-  'text' Table

date	text
1/1/2019	text1
2/1/2019	text2
...	...
1/1/2020	text4
12/31/2020	text5

- Date Table

date	week_year_no	year
O1/01/2019	1	2019
...	...	...
31/12/2020	52	2020

I would like to extract the right text from the text table according to the current week number and the year.

I tried

Text_context = CALCULATETABLE(Table,Table[ week_year_no]=WEEKNUM(TODAY())

or

Text_context = FILTER(Table,Table[ week_year_no]=WEEKNUM(TODAY())

Do you know how I could filter this table according to the year and the week number?
And generally how it's possible to apply several filters ?

A response would be appreciated

Thanks

Noliverte

[noliverte]

Hi amitchandak,

Thanks for taking time to respond to me,

I changed my table like this

If I usenew measure = CALCULATE(countrows(Explications),filter(Calendrier,Calendrier[Week-num]=WEEKNUM(TODAY())-1))I don't have only "text week50", but all the table...

If I change in the visualization menu, I can't have only "text week50"

Do you have an idea ?

Have a nice day

Noliverte

[amitchandak]

@noliverte , this should give you a new table as weeknumber is in date table

new table = CALCULATETABLE(Table,filter(Date,date[week_year_no]=WEEKNUM(TODAY())))

new measure = CALCULATE(countrows(Table),filter(Date,date[week_year_no]=WEEKNUM(TODAY())))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[noliverte]

Hi amitchandak,

Thanks for taking time to respond to me,

I changed my table like this

If I use

new measure = CALCULATE(countrows(Explications),filter(Calendrier,Calendrier[Week-num]=WEEKNUM(TODAY())-1))

I don't have "text week50"...

If I change in the visualization menu, I can't have only "text week50"

Do you have an idea ?

Have a nice day

Noliverte

[Ashish_Mathur]

Hi,

Against each week in the Date Table there would be 7 rows and therefore when you filter on the current week, there will be multiple text entries in the text table.  Which text entry do you want as the final result.  Please show the expected result. 

[noliverte]

Hi Ashish_Mathur,

Thanks for taking time to respond to me, 

In my final result, I'd like to have a simple table in powerbi with the text corresponding to the number of the previous week.

Table : Explications

I would like to have each week the text which corresponds to the previous week.
How to build this measure?

(this week number is 51)

I'd like to have automatically simple text with "text week 50"

Have a nice day

Noliverte

[v-yingjl]

Hi @noliverte ,

Not certain what is your table look like but you can try to create a measure like this:

explications =
IF (
    SELECTEDVALUE ( 'Calendar'[Weeknum] )
        = WEEKNUM ( SELECTEDVALUE ( 'Table'[Date] ), 2 ),
    "text week "
        & ( WEEKNUM ( SELECTEDVALUE ( 'Table'[Date] ), 2 ) - 1 )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[noliverte]

Hello v-yingil,

Thank you for your explanation and your file, but it's not exactly what I expect.

In view report I'd like one table (without filters).

Table has 2 cols (date, explications)
explications isn't a measure (Data come from an Google spreadsheet file)

Table

I would like to create a measure that would be able to display only the text (taken from the table above) from the previous week.

(if the current week number is 50, the displayed text will be "text week 49")

Thank you for your help and advice

Noliverte

[v-yingjl · Accepted solution]

Hi @noliverte ,

When I reply you the current week is 51, so you want to show data about text 50 right?

You can create a measure like this, put it in the table visual filter and set its value as 1:

A = 
IF (
    WEEKNUM ( TODAY (), 2 ) - 1
        = WEEKNUM ( SELECTEDVALUE ( 'Table'[Date] ), 2 ),
    1,
    0
)

Attached the sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[noliverte]

Hi v-yingil,

Congratulation it works fine.
Thank you very much for your precious help.

I've added a if statement to fit with my final report

A = 
IF(
    YEAR(TODAY())
        = YEAR(SELECTEDVALUE('Table'[Date])),
        IF (
            WEEKNUM ( TODAY (), 2 ) - 1
                = WEEKNUM ( SELECTEDVALUE ( 'Table'[Date] ), 2 ),
            1,
            0
        ),
        0
)

One last request with your pbix.
In my final powerbi file, I have to hide the date fied to only show explanations...

Do you know how i could remove this field from my table?
In your file, if I remove this date field from the table, the explanation field also disappears

Have a nice day

Noliverte

[noliverte]

Hi,

I just found a way to hide date field...
I replace Table viewer by HTML5 viewer

And the table vs html5 component
left : Table
right : html 5 component