within 30 days calc column

[jutu]

Hi,

I have the below SQL query which needs to be built in PBI. If i can add a column it would filter a lot of unnecessary data. How would you go about it?

FROM [dbo].[Operations_LETTERS_SENT_YTD]

WHERE DATEDIFF(dd, DATEADD(dd, 0, DATEDIFF(dd, 0, [DT_LETTER_SENT])),DATEADD(dd, -1, GETDATE())) <= 30

ORDER BY CAST([DT_LETTER_SENT] AS DATE), [LETTER_CODE]

 

[GraH - Guido]

Hi jutu,

Kind off the same way. Hopefully it's a date column then something of this sort might get you in the right direction (I'm a bit confused after a day of data crunching by your nested datediff and dateadd):

Date.AddDays([date], integer),
Duration.Days([date] - [day]) // gives you an integer value
Table.SelectRows(table, each Date.IsInPreviousNDays([date], 30)) //enables filtering on a date column
Table.SelectRows(table, each "any calculation" <=30) // you do not need to have a column to filter on

 

[jutu]

Hey thank you Guido. I know my questions are not cut clear but trying to get better at it sorry

 

[jutu]

Hi. It gives me error and I think it will be easier if it's not filtered just to calculate the difference in dates from a date to today's date. I've googled it but not much success finding a similar issue.....probably not looking right
I have created the below calculated column but need it in the M language as a custom column if you can assist please;

LettersSent30DaysAgo = DATEDIFF('Semantic FactCommunications'[LetterDate], TODAY(), DAY)

 

[jutu]

I have tried the below but it gives me all negative numbers?

Number.From(Date.From([LetterDate])) - Number.From(Date.From(DateTime.LocalNow()))

 

[jutu]

Ok I have just inverted the order of the two...........parameter and function you would call it? between LetterDate and today's date