Help creating 90 day rolling calendar lookup table.

[Imthedan]

Hello,

could someone point me in the direction of creating a rolling 90 day calendar as a lookup table? 

thank you. 

[amitchandak]

@Imthedan , what is the objective of lookup,

if you want to sum for lat 90 days

Rolling 90 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-90,Day))

[Imthedan]

I get a rolling 90 day excel file sent to me daily.

Basically, I want to create a calendar lookup table that allows me to sort this data by last 90, last 60, ect... I want to use a date slider mostly, but I will also use measures to sum/count the data.

For now, I just need to create a rolling 90 day calendar lookup table.

[edhans · Accepted solution]

In Power Query, this will give you a 90 day date range, always moving with each refresh.

let
    Source = 
    {
        Number.From(Date.AddDays(DateTime.Date(DateTime.LocalNow()), -90))..
        Number.From(DateTime.Date(DateTime.LocalNow()))
    },
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}})
in
    #"Changed Type"

in DAX, you could use create a new table and use:

Table 2 = 
CALENDAR(
    Today()-90,
    Today()
)

Either will work as well as the other. If you are going to be adding columns to this table, Power Query is your best bet unless you ensure you use ADDCOLUMNS() with DAX. Don't manually add calculated columns. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Imthedan]

Thank you!

[edhans]

Glad to assist @Imthedan