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.
Hi.
I have a question for you.
I have a Table in Excel with data from 2009 to today (Just monday to friday information). Following is an example of the table:
FECHA COLCAP GrupoArgos
Wednesday, December 23, 2009 | 1364.13 | 18980 |
Thursday, December 24, 2009 | 1372.72 | 18900 |
Friday, December 25, 2009 | 1372.72 | 18900 |
Monday, December 28, 2009 | 1365.06 | 18860 |
Tuesday, December 29, 2009 | 1366.35 | 18940 |
Wednesday, December 30, 2009 | 1366.85 | 19000 |
Thursday, December 31, 2009 | 1366.85 | 19000 |
Friday, January 1, 2010 | 1366.85 | 19000 |
Monday, January 4, 2010 | 1374.28 | 19500 |
Tuesday, January 5, 2010 | 1380.46 | 19560 |
Wednesday, January 6, 2010 | 1381.35 | 19780 |
Thursday, January 7, 2010 | 1368.91 | 19980 |
Friday, January 8, 2010 | 1374.72 | 20300 |
Monday, January 11, 2010 | 1374.72 | 20300 |
Tuesday, January 12, 2010 | 1369.53 | 20400 |
Wednesday, January 13, 2010 | 1379.91 | 20900 |
Thursday, January 14, 2010 | 1386.91 | 20980 |
Friday, January 15, 2010 | 1382.56 | 20340 |
What I want is to use that information to Power BI. But I just need the information of the last year, 6 months, 3 months and last 30 days. (Basically I want 4 tables with the timeframes described before). So, when I add new information, those tables can update automatically with those timeframes. As I need to create calculated columns, that's why I need it that way.
I appreciate your assistance.
Solved! Go to Solution.
@ChristianJul - Test
Last Year Table =
VAR __Today = TODAY()
VAR __Min = DATE(YEAR(__Today)-1,MONTH(__Today),DAY(__Today))
RETURN
CALENDAR(__Min,__Today)
Six Month Table =
VAR __Today = TODAY()
VAR __EOM = EOMONTH(__Today,-6)
VAR __Min = DATE(YEAR(__EOM),MONTH(__EOM),DAY(__Today))
RETURN
CALENDAR(__Min,__Today)
Six Month Table =
VAR __Today = TODAY()
VAR __EOM = EOMONTH(__Today,-3)
VAR __Min = DATE(YEAR(__EOM),MONTH(__EOM),DAY(__Today))
RETURN
CALENDAR(__Min,__Today)
Thirty Day Table =
Six Month Table =
VAR __Today = TODAY()
VAR __Min = __Today - 30
RETURN
CALENDAR(__Min,__Today)
@ChristianJul - Test
Last Year Table =
VAR __Today = TODAY()
VAR __Min = DATE(YEAR(__Today)-1,MONTH(__Today),DAY(__Today))
RETURN
CALENDAR(__Min,__Today)
Six Month Table =
VAR __Today = TODAY()
VAR __EOM = EOMONTH(__Today,-6)
VAR __Min = DATE(YEAR(__EOM),MONTH(__EOM),DAY(__Today))
RETURN
CALENDAR(__Min,__Today)
Six Month Table =
VAR __Today = TODAY()
VAR __EOM = EOMONTH(__Today,-3)
VAR __Min = DATE(YEAR(__EOM),MONTH(__EOM),DAY(__Today))
RETURN
CALENDAR(__Min,__Today)
Thirty Day Table =
Six Month Table =
VAR __Today = TODAY()
VAR __Min = __Today - 30
RETURN
CALENDAR(__Min,__Today)
@Anonymous , Not very clear, you can use relative date slicer to show this kind of information.
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
or you can create rolling measures with ate table
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-30,Day))
Hi. Thank you for your response. However, what I'm looking for is to have a Table of the last year (for example from 9/10/2019 to 9/10/2020). I need it in table (Creating a table not with a measure), because I need to create calculated columns.
I need to use 'Create Table' Feature.
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
97 | |
85 | |
70 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |