Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello,
could someone point me in the direction of creating a rolling 90 day calendar as a lookup table?
thank you.
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you!
Glad to assist @Imthedan
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYour insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
92 | |
91 | |
84 | |
80 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |