Filter Table by Rows

[Anonymous]

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.

[Greg_Deckler · Accepted 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)

[amitchandak]

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

[Anonymous]

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