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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.