Forum Discussion

VK's avatar
VK
New Member
10 years ago
Solved

Display this week & this month data

Hello,   I am new to Power BI and on a graph I would like to display data for this week. For example, if the week starts on Monday and today is Monday, it will show data for Monday. If today is Tue...
Need Help
  • elliotdixon's avatar
    elliotdixon
    10 years ago

    HI VK

    This is pretty easy - one of the main reasons I like Power BI.

    First thing is that you need to have another table that has just Dates in it.

    Create a link between the date data in your Opportunity table and then you can create calculated colums in the dates table that will provide you with the answers.

     

    1. Dates Table

    Good table to start out with is http://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query/
    this will give you Date,DayOfMonth,Year,DayOfWeekNum etc.

     

    Then create the following

     

    DAX Measures

    Today:=DATE(year(now()),MONTH(NOW()), DAY(NOW()))

    DAX Calculated Columns

    IsInCurrentYear

    =if(YEAR(NOW())= [Year],1,0)

     

    WeekOfYearNumber

    =WEEKNUM([Date],2)


    IsInCurrentWeek

    =if([isInCurrentYear] && WEEKNUM(NOW())=[WeekOfYearNumber],1,0)

     

    IsInCurrentYear = if(YEAR(NOW())= [Year],1,0)
    // Column to see if it is the current year

     

    IsInLastWeek

    =if([isInCurrentYear] && (WEEKNUM(NOW())-1)=[WeekOfYearNumber],1,0)

     

    IsLast30Days

    =if(AND([Date]>=[Today]-30,[Date]<=[Today] ),1,0)

     

    YearWeekNum = Concatenate(Dates[Year],Dates[WeekOfYearNumber])

     

    WTD = IF(CALCULATE(VALUES(Dates[YearWeekNum]),Dates[Date]=TODAY()-1,ALL(Dates))=Dates[YearWeekNum]

    && Dates[Date]<=TODAY()-1,"WTD",BLANK())

    // shows if its in the current Week To Date - can use as a filter

     

    RelativeDate = [Date]-Today()

    //shows the difference in days between today and a date

    // good for looking into the future or so many days back in the past.

     

    EOM = EOMONTH(Dates[Date],0)
       //Add a column that returns true if the date on rows is the current date

     

    IsLast7Days = if(AND([Date]>=[Today]-7,[Date]<=[Today]),1,0)

    // 1 if is in the last 7 days

     

    IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date]))

    //Column to see if it is the day today.

     

    I use these extra columns all the time.

    for your issue you can then just add filters on the page or the report for what you want.

     

    Hopefully this will work.

    Rgds

    ED

VK's avatar
VK
New Member
10 years ago

Thank you for the tip. I was referring to one of the Salesforce object tables called "Opportunity", where I wanted to specify different time periods on the CloseDate column for different graphs. 

 

opportunity object.png

elliotdixon's avatar
elliotdixon
Responsive Resident
10 years ago

HI VK

This is pretty easy - one of the main reasons I like Power BI.

First thing is that you need to have another table that has just Dates in it.

Create a link between the date data in your Opportunity table and then you can create calculated colums in the dates table that will provide you with the answers.

 

1. Dates Table

Good table to start out with is http://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query/
this will give you Date,DayOfMonth,Year,DayOfWeekNum etc.

 

Then create the following

 

DAX Measures

Today:=DATE(year(now()),MONTH(NOW()), DAY(NOW()))

DAX Calculated Columns

IsInCurrentYear

=if(YEAR(NOW())= [Year],1,0)

 

WeekOfYearNumber

=WEEKNUM([Date],2)


IsInCurrentWeek

=if([isInCurrentYear] && WEEKNUM(NOW())=[WeekOfYearNumber],1,0)

 

IsInCurrentYear = if(YEAR(NOW())= [Year],1,0)
// Column to see if it is the current year

 

IsInLastWeek

=if([isInCurrentYear] && (WEEKNUM(NOW())-1)=[WeekOfYearNumber],1,0)

 

IsLast30Days

=if(AND([Date]>=[Today]-30,[Date]<=[Today] ),1,0)

 

YearWeekNum = Concatenate(Dates[Year],Dates[WeekOfYearNumber])

 

WTD = IF(CALCULATE(VALUES(Dates[YearWeekNum]),Dates[Date]=TODAY()-1,ALL(Dates))=Dates[YearWeekNum]

&& Dates[Date]<=TODAY()-1,"WTD",BLANK())

// shows if its in the current Week To Date - can use as a filter

 

RelativeDate = [Date]-Today()

//shows the difference in days between today and a date

// good for looking into the future or so many days back in the past.

 

EOM = EOMONTH(Dates[Date],0)
   //Add a column that returns true if the date on rows is the current date

 

IsLast7Days = if(AND([Date]>=[Today]-7,[Date]<=[Today]),1,0)

// 1 if is in the last 7 days

 

IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date]))

//Column to see if it is the day today.

 

I use these extra columns all the time.

for your issue you can then just add filters on the page or the report for what you want.

 

Hopefully this will work.

Rgds

ED

  • AdrianThread's avatar
    AdrianThread
    Frequent Visitor
    10 years ago

    Thank you so much elliotdixon !

     

    These have been invaluable in designing my first forays into Power BI and helping me get my head around DAX for the first time.

     

    In case other folks are looking here for an 'IsInCurrentFiscalYear' calculated column that returns 1 or 0, here's one that's working for me:

     

    IsInCurrentFY = CALCULATE(sumx(dates,if(
		dates[year] = year(today())-1
		&& dates[month] >= 7, 1,
			if(dates[Year]=YEAR(TODAY())
			&& month(today()) < 7
			&& month(Dates[Date]) < 7, 1,
				if(dates[year]=year(today()) 
				&& month(today()) > 6
				&& Dates[Month] > 6, 1,
	0)))))

     

    Hope this checks out...

     

    Thanks again,

    Adrian

    • cwayne758's avatar
      cwayne758
      Helper IV
      10 years ago

      I find it easier to use:

       

      =Date.IsInCurrentYear 

      =Date.IsInCurrentWeek

      =Date.IsInCurrentMonth

       

      all of these functions can be used in the Query Editor.

       

      Just another method of creating the same output.

      • kenthub's avatar
        kenthub
        Helper I
        9 years ago

        cwayne758 Hi can you show me a screen grab of where you put this along with an example use of the funciton? Thanks in advance!

    • greggyb's avatar
      greggyb
      Resident Rockstar
      10 years ago

      AdrianThread, assuming you have a FiscalYear field in your date dimension:

       

      // Boolean flag, true in current fiscal year
CurrentFY =
VAR
    CFY =
        LOOKUPVALUE(
            DimDate[FiscalYear]
            ,DimDate[Date]
            ,TODAY()
        )
RETURN
DimDate[FiscalYear] = CFY



// Integer flag, 1 in current fiscal year
CurrentFY =
VAR
    CFY =
        LOOKUPVALUE(
            DimDate[FiscalYear]
            ,DimDate[Date]
            ,TODAY()
        )
RETURN
1 * (DimDate[FiscalYear] = CFY)

      It's best to avoid duplicating the same logic in multiple fields. Since you likely have the same logic in a [FiscalYear] field as in your definition of [IsInCurrentFiscalYear], you'd have to worry about keeping them in sync if you find an error. With the constructions above, you only define the fiscal year logic in one place and updates to it are automatically propagated.

      • AdrianThread's avatar
        AdrianThread
        Frequent Visitor
        10 years ago

        Thanks greggyb, I don't have a fiscal year field in my raw data so I needed to make a measure. I certainly agree with not duplicating logic over multiple fields and measures. Thanks again.

  • B_Real's avatar
    B_Real
    Advocate IV
    9 years ago

    Great post by elliotdixon! So you've got a bunch of filters to determine whether something falls within last 7 days, or last 30 days, or this year. This is fine if we want to apply just one of the filters to the dashboard. But what if we want the user to select which filter to show? For example, say we have these three filters:

     

    IsInCurrentWeek

    IsInLastWeek

    IsLast30Days

     

    Can we show a single filter in the dashboard so that the user can select which one of those three filters (current week, last week, or last 30 days)?

     

    In Tableau, these 'previous week', 'previous month', 'previous 6 month' (etc.) type selectors are already built in.

     

  • Dhilip's avatar
    Dhilip
    Regular Visitor
    9 years ago

    IsLast7Days = if(AND([Date]>=[Today]-7,[Date]<=[Today]),1,0) // 1 if is in the last 7 days

     

     

    Can we make the above formula to show a cumulative total for the last 7 days....

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you so much!