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

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.

  • elliotdixon's avatar
    elliotdixon
    Responsive Resident
    10 years ago

    Hi AdrianThread - great to hear the date codes are working for you.

    I tried your code for Fiscal Year however got an error.

     

    DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

     

    I think it has to do with the Month - what is the format of your month column?
    I have MonthNumberOfYear and its just a whole number 1 - 12.

     

    Cheers

     

    • greggyb's avatar
      greggyb
      Resident Rockstar
      10 years ago

      Both year and month in your date dimension must be integers with the whole number data taype in AdrianThread's calculated column.

    • avelio's avatar
      avelio
      Helper II
      9 years ago

      elliotdixon Hello!

       

      Thank you for your great suggestions. I've also found value in them!

       

      Is there any possibility to create a measure which displays sum of sales for current week and month?

      I'm trying to do this because in the same screen i need to display both values in the same screen and using a slicer does not work for me because it will filter all my data.

       

      I've been searching the web for the past days to come up with a formula but no succes.

      It would be of great help to me if i could find a solution to this.

       

      BR,

      Andrei