HELP ! Convert texte(string) to date

[tosuns]

Hello Dear PBI Community
I have a data from SAP.
The date data are on week.year format (example: 01.2016 for the first week of 2016 year).
I have to make a report on which I must filtered data on last 16 week.
I cannot make a relative filtering given that date are on text format.
I tried to make a dynamic calendar on PowerQuery and add a relationship. But on this situation, the data doesnt display.

I am a trainee, and I have to finish this report. Can someone help me please !

Thank you in advance !

[v-juanli-msft · Accepted solution]

Hi @tosuns 

Use DAX functions to create a new table (Modeling->new table)

Table = ADDCOLUMNS(CALENDAR(DATE(2019,1,1),DATE(2019,12,31)),"YEAR",YEAR([Date]),"WEEKNUM",WEEKNUM([Date],2))

In this table , add a column

week.year = IF([WEEKNUM]<10,0&[WEEKNUM]&"."&[YEAR],[WEEKNUM]&"."&[YEAR])

Cretae many to many relationship

Add date from this table to a slicer

select relative slicer

Best Regards
Maggie

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