Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

HELP ! Convert texte(string) to date

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 !
Need Help
Show and Tell
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    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.