Forum Discussion
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 period and Yearover Year.
For example Current Week W34 , Last Week-W33 and i want change% from W33 to w34 and change % from Last YearWeek 34 to this year W34 and same for Months over Months and same month last year and quarter etc.
How do i calculate measure for that.
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
1 Reply
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