Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a formula today to find out previous week sales from selected week.
Previous Week Sales = VAR CurrentWeek = SELECTEDVALUE(Date_table[Week Nr]) VAR CurrentYear = SELECTEDVALUE(Date_table[Year]) VAR MaxWeekNumber = CALCULATE(MAX(Date_table[Week Nr]);ALL(Date_table)) // If week = 1, then it will calculate against week 52 or 53 fix RETURN SUMX( FILTER(ALL(Date_table); IF(CurrentWeek = 1; Date_table[Week Nr] = MaxWeekNumber && Date_table[Year] = CurrentYear - 1; Date_table[Week Nr] = CurrentWeek -1 && Date_table[Year] = CurrentYear) ); [Sum Revenue])
Solved! Go to Solution.
I tried that but got:
The expression contains multiple columns, but only a single column can
be used in a True/False expression that is used as a table filter expression.
However, I tried out something and ended up with this, that works:
3 Previous QTY Week Sales = VAR CurrentWeek = SELECTEDVALUE(Date_table[Week Nr]) VAR CurrentYear = SELECTEDVALUE(Date_table[Year]) VAR MaxWeekNumber = CALCULATE(MAX(Date_table[Week Nr]);ALL(Date_table)) // If week = 1, then it will calculate against week 52 or 53 fix RETURN SUMX( FILTER(ALL(Date_table); IF(CurrentWeek = 1; Date_table[Week Nr] = MaxWeekNumber && Date_table[Year] = CurrentYear - 1; Date_table[Week Nr] < CurrentWeek && Date_table[Week Nr]>CurrentWeek -4 &&
Date_table[Year] = CurrentYear) ); [Sum Revenue product])
Thanks for your help, learned something new today 🙂
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Hi,
Thanks for reaching out!
I tried this:
3 Previous Week Sales = VAR MAXYEAR = MAX(Date_table[Year]) VAR MAXWEEK = MAX(Date_table[Week Nr]) VAR TMPTABLE = CALCULATETABLE(Date_table;ALL(Date_table[Year]);ALL(Date_table[Week Nr])) RETURN SUMX(FILTER(TMPTABLE;Date_table[Year] = MAXYEAR && Date_table[Week Nr] <= MAXWEEK); [Sum Revenue])
This gave me YTD sales in week from selected week, but I'm trying to find out the 3 last weeks sales from selected week 🙂
I think you want:
3 Previous Week Sales = VAR MAXYEAR = MAX(Date_table[Year]) VAR MAXWEEK = MAX(Date_table[Week Nr]) VAR TMPTABLE = CALCULATETABLE(Date_table;Date_table[Year]=MAXYEAR && Date_table[Week Nr]<MAXWEEK && Date_table[Week Nr]>MAXWEEK-3)) RETURN SUMX(TMPTABLE; [Sum Revenue])
Something like that. Might have an issue with year roll-over.
I tried that but got:
The expression contains multiple columns, but only a single column can
be used in a True/False expression that is used as a table filter expression.
However, I tried out something and ended up with this, that works:
3 Previous QTY Week Sales = VAR CurrentWeek = SELECTEDVALUE(Date_table[Week Nr]) VAR CurrentYear = SELECTEDVALUE(Date_table[Year]) VAR MaxWeekNumber = CALCULATE(MAX(Date_table[Week Nr]);ALL(Date_table)) // If week = 1, then it will calculate against week 52 or 53 fix RETURN SUMX( FILTER(ALL(Date_table); IF(CurrentWeek = 1; Date_table[Week Nr] = MaxWeekNumber && Date_table[Year] = CurrentYear - 1; Date_table[Week Nr] < CurrentWeek && Date_table[Week Nr]>CurrentWeek -4 &&
Date_table[Year] = CurrentYear) ); [Sum Revenue product])
Thanks for your help, learned something new today 🙂
I tried that but got:
The expression contains multiple columns, but only a single column can
be used in a True/False expression that is used as a table filter expression.
However, I tried out something and ended up with this, that works:
3 Previous QTY Week Sales = VAR CurrentWeek = SELECTEDVALUE(Date_table[Week Nr]) VAR CurrentYear = SELECTEDVALUE(Date_table[Year]) VAR MaxWeekNumber = CALCULATE(MAX(Date_table[Week Nr]);ALL(Date_table)) // If week = 1, then it will calculate against week 52 or 53 fix RETURN SUMX( FILTER(ALL(Date_table); IF(CurrentWeek = 1; Date_table[Week Nr] = MaxWeekNumber && Date_table[Year] = CurrentYear - 1; Date_table[Week Nr] < CurrentWeek && Date_table[Week Nr]>CurrentWeek -4 &&
Date_table[Year] = CurrentYear) ); [Sum Revenue product])
Thanks for your help, learned something new today 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |