Paginated Report, Covert text to date so I can filter it to the last 7 days of data on a table.

[Dribblej]

Hello, 

I am new to BI Paginated reports. I set up a data source from an existing power bi dataset. When I query the field into report builder the dates are showing up as data type = text. 

Can I covert the text to data type = date and where do I do that? In a filter on the field, the table or in the query itself?

Any help would be great appreciated. I have tried using CDate, FormatDateTime, DateTime.Parse and I think Im just doing something wrong to make the change. Examples I used in the filter expression on the table are: 

=FormatDateTime(Fields!Actual_Close_Date.Value,DateFormat.GeneralDate)

Goal: Covert 'Actual_Close_Date' field from my bi data set into a date type and then filter to see only the last 7 days of data or the last week. 

Thank you so much!

[parry2k · Accepted solution]

@Dribblej yes that DAX query will work.

Yes for #2 if that is what you need.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Dribblej here is what I will do to keep it dynamic

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Won"}, 'Opportunities'[Status])

VAR ToCalendarDate = TODAY()
VAR FromCalendarDate = TODAY() - 7

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('Opportunities'[Actual Close Date])),
      AND(
        'Opportunities'[Actual Close Date] >= DATEVALUE (FromCalendarDate ),
        'Opportunities'[Actual Close Date] < DATEVALUE ( ToCalendarDate )
      )
    )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Dribblej]

Thank you!

1. What would be different if I used this...

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Won"}, 'Opportunities'[Status])

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('Opportunities'[Actual Close Date])),
     
        'Opportunities'[Actual Close Date] >= Today()-7
      
    )

2. When I do this im assuming it is now making this tables dataset filtered to the DAX query. If i want to re use this dataset in an additional table I would I would have to recall the same fields without DAX filters applied, correct? I understand this is bad practice to duplicate data and it slows down the report. 

[parry2k]

@Dribblej it should be pretty straight forward from here. you have the key components in place, just a matter of replacing them with variables to get this going.

[parry2k]

@Dribblej you can change the enhance the DAX query, add a variable with TODAY() date and calculate another variable for -7 days and change that fixed value with these variables.

[parry2k]

@Dribblej the best would be to create a table in the Power BI desktop and try to mimic the output you are looking for, once it is working there, use a performance analyzer, copy the DAX query and use that in Report Builder.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Dribblej]

@parry2k, That was a super sick suggestion. Thank you. Now I run into the next problem that may mean this solution path doesnt work. 

After copying the query from the performance analyzer and using it on the table I can see that the dates are fixed to a hard date. In power bi desktop I used a relative date filter on the field "actual close date" of 'in the last '1' week". 

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"Won"}, 'Opportunities'[Status])

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('Opportunities'[Actual Close Date])),
      AND(
        'Opportunities'[Actual Close Date] >= DATE(2021, 2, 6),
        'Opportunities'[Actual Close Date] < DATE(2021, 2, 13)
      )
    )

Im concerned this is not going to be dynamic in report builder with this output. Im quite sure actually it wont adjust the date going forward from the time I pasted in the DAX statement to the query on report builder. 

Ideas there?? 

Thank you so much!

[Dribblej]

Could I just create another column in power bi desktop on my calendar of relative day, and use that field to filter in the query for days 0 to -7? Or a relative week filter column with the same logic. Then pull that into the query that I am getting from performance analyzer and paste that.