Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
po
Post Prodigy
Post Prodigy

Previous value for same day last week and last week

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.

 

 

 

1 ACCEPTED 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Christian18
Regular Visitor

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:

 

Christian18_0-1658344172671.png

 

Aguardo tus comentarios. Desde ya, muchas gracias. 

 

Cordialmente, Christian

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.