Show moving date range starting one week prior to today and ending two weeks after today?

[iDataDrew]

Hi, I'm trying to put together a weather forecast for different states.  I have a lot of historical data, however the only dates I need to visualize are those starting 6 days prior to TODAY() and ending 14 days after TODAY().  Here's what I've tried so far with no success:

CurrentForecast = DATESINPERIOD(Weather[Date],TODAY()-6,20,DAY)

CurrentForecast = DATESBETWEEN(Weather[Date],TODAY()-6,TODAY()+14)

[amitchandak]

You date formula seems correct, what are formula's using those. And what is the issue, getting blank data or wrong data.

Can you share sample data and sample output.

[KNP]

Hi @iDataDrew ,

If you're open to solving this in M and you're not able to limit the data at the source, you could create a simple date table...

let
    Source = DateTime.Date(DateTime.FixedLocalNow()),
    StartDateAsNumber = Number.From(Source) - 6,
    EndDateAsNumber = Number.From(Source) + 14,
    DateListAsNumber = {StartDateAsNumber..EndDateAsNumber},
    #"Converted to Table" = Table.FromList(DateListAsNumber, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
    #"Changed Type"

and then 'merge' this with your data table with an INNER JOIN.

You could also parameterize the start and end (-6 and +14) if you wanted to make it easy to change.

Regards,

Kim

[kedarn1991]

I would pre- populate this data before bringing it into power bi. 

If you are using SQL you can easily only popupate the past 7 days data.

select * 

FROM servername.schema.TableName

where Datediff(dd,Date,getDate()) <= 6

For the 14 days forcast

select *

FROM servername.schema.TableName

where Date <= DateAdd(dd,14,getDate()) and Date > getDate() --Excluding today, because today is already included in past 7 days.

 Hope that helps. Thank you.