Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
We have a sales table which will have many weeks worth of data in it
YearWeek
Shop
Day
Amount Supplied
Amount Sold
and wish to show for each day of week the amount sold for the corresponding day of the previous week
when view at day level
YearWeek Shop Day Amount Sold Previous Amount Sold
201901 A SUN 100 50
Similarly when view at week level wish to show the amount sold for the previous week.
How is this best achieved?
Have used below in past but this not working here and wonder if better approach
calculate(SUMX(sales,sales[amount sold]) ,filter(all
(calendar),calendar[Date]=MAX(calendar[Date])-7) )
When use above just shows a value for one week - other weeks blank.
Solved! Go to Solution.
I think you want:
Measure = VAR __max = MAX(calendar[Date]) RETURN calculate(SUMX(sales,sales[amount sold]) ,filter(all (calendar),calendar[Date]=__max-7) )
If you have the week in the visual and this, then the first line should be context constrained to the row.
Hola Greg, cómo estás?
Necesito si por favor me podrás ayudar con este tema:
Tengo que comparar las ventas de la semana anterior con las de la semana actual y necesito que solo me muestre los datos para los días que realmente han transcurrido de la semana actual, es decir, para esta semana solo quiero ver los datos del lunes y del martes, dado que estamos a miércoles y todavía no se completó la jornada. Mañana sí voy a necesitar que también se vean los datos del miércoles y así sucesivamente que se vayan agregando los datos de los días "cumplidos".
Lo que logré hasta ahora me resulta esto que, como se vé, la tabla me la completa con los datos de los días de la semana anterior, lo cual quiero evitar y que solo se vea cada nuevo día de la semana actual que se vaya cumpliendo:
Aguardo tus comentarios. Desde ya, muchas gracias.
Cordialmente, Christian
If you want to do this as a column you might look into EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Also 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...
Finally, you might want to use Sequential to make the math easier: https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231
Thanks for reply ande useful linkss.
Finding the fromula below works when have a slicer aqnd selecting a week will calculate the value relative to that previous week but issue have is if want to say have a few weeks showing at once below will only show one value as the max of the latest date shown is used.
e.g if 201904 will show 201903 but 201902 and 201901 will be blank.
calculate(SUMX(sales,sales[amount sold]) ,filter(all
(calendar),calendar[Date]=MAX(calendar[Date])-7) )
I think you want:
Measure = VAR __max = MAX(calendar[Date]) RETURN calculate(SUMX(sales,sales[amount sold]) ,filter(all (calendar),calendar[Date]=__max-7) )
If you have the week in the visual and this, then the first line should be context constrained to the row.
Looks to be working now not sure why wasn't before
Thansk - yes this is what I thought. However, when do this if have say weeks 201901 to 201904 see that the row with 201903 is populated (as has max date-7) assuming in week 201904 but other rows are blank for previous week value.