Forum Discussion

astha12's avatar
astha12
New Member
9 years ago
Solved

power bi

Hi I have Weighted measure like this Issue Type Percent of Total = DIVIDE ([Total Count],Calculate([Total Count],ALL(Sales '[Purchase]))) and i am trying to calculate the change for Period over perio...
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    Hi astha12,

     

    Since there are no PREVIOUSWEEK function in DAX, so you need to calculate the Year and week and weektotal in your data model, and then use LOOKUPVALUE function to get previous week value and last year same week value.

    I have two tables in my sample data model. In your date table, create two columns.
    Year = YEAR('Date'[CalendarDate])
    WeekNumber = WEEKNUM('Date'[CalendarDate])

    then create a new table
    Table = SUMMARIZE('Date','Date'[Year],'Date'[WeekNumber],"Weektotal",calculate(SUM(Sales[SalesAmount]),ALLEXCEPT('Date','Date'[Year],'Date'[WeekNumber])))
    Create three measures.
    WeekTotalSaleAmount = calculate(SUM(Sales[SalesAmount]),ALLEXCEPT('Date','Date'[Year],'Date'[WeekNumber]))
    PreviousWeek = LOOKUPVALUE('Table'[Weektotal],'Table'[Year],IF(MAX('Date'[WeekNumber])=1,MAX('Date'[Year])-1,MAX('Date'[Year])),'Table'[WeekNumber],IF(MAX('date'[WeekNumber])=1,53,MAX('Date'[WeekNumber])-1))

    PreviousYearWeek = LOOKUPVALUE('Table'[Weektotal],'Table'[Year],MAX('date'[Year])-1,'Table'[WeekNumber],MAX('date'[WeekNumber]))

     

     

    Regards,

    Charlie Liao

     

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

Hi astha12,

 

Since there are no PREVIOUSWEEK function in DAX, so you need to calculate the Year and week and weektotal in your data model, and then use LOOKUPVALUE function to get previous week value and last year same week value.

I have two tables in my sample data model. In your date table, create two columns.
Year = YEAR('Date'[CalendarDate])
WeekNumber = WEEKNUM('Date'[CalendarDate])

then create a new table
Table = SUMMARIZE('Date','Date'[Year],'Date'[WeekNumber],"Weektotal",calculate(SUM(Sales[SalesAmount]),ALLEXCEPT('Date','Date'[Year],'Date'[WeekNumber])))
Create three measures.
WeekTotalSaleAmount = calculate(SUM(Sales[SalesAmount]),ALLEXCEPT('Date','Date'[Year],'Date'[WeekNumber]))
PreviousWeek = LOOKUPVALUE('Table'[Weektotal],'Table'[Year],IF(MAX('Date'[WeekNumber])=1,MAX('Date'[Year])-1,MAX('Date'[Year])),'Table'[WeekNumber],IF(MAX('date'[WeekNumber])=1,53,MAX('Date'[WeekNumber])-1))

PreviousYearWeek = LOOKUPVALUE('Table'[Weektotal],'Table'[Year],MAX('date'[Year])-1,'Table'[WeekNumber],MAX('date'[WeekNumber]))

 

 

Regards,

Charlie Liao