Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Imthedan
Helper II
Helper II

Help creating 90 day rolling calendar lookup table.

Hello,

 

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

thank you. 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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))

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thank you!

Glad to assist @Imthedan 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors