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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nosaj03
Helper II
Helper II

Creating a Measure based on Previous day

Hello all!

 

I have a Sales table of employees by day by item category and total time on sale

 

I want to create a measure that shows the averge time for sales for by employee for the previous day based on current day.

 

Example

DateEmployee GroupItem groupSalesTime 
5/19/2020JoeTechIOS3819
5/19/2020JoeTechAndroid112247
5/19/2020JoeTechWindows3111056
5/19/2020JoeTechGaming1176
5/18/2020JoeTechIOS4919
5/18/2020JoeTechAndroid15254
5/18/2020JoeTechWindows2612001
5/18/2020JoeTechGaming14201

 

I want to find out the average time for Joe on 5/18 so the formula is sum(Time)/sum(sales)

 

I've been using the following formula

Calculate(divide(sum(Time),sum(Sales),0),filter(date = now()-1)) and it returns nothing. 

 

Other times its gives me the "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value" or the Divide by Zero error. 

 

I'm stuck here. Any help would be greatly appreciated.

 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@nosaj03 

 

You should use Today() instead of Now(), because Now()-1 return current time-1 second, not previous day.  And for the sample you provided, you can test it by replace Today() with Date(2020,5,19) to check the average of 5/18

 

Measure Calculate(divide(sum([Time ]),sum([Sales]),0),filter('Table',[Date]= TODAY()-1))

 

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
V-pazhen-msft
Community Support
Community Support

@nosaj03 

 

You should use Today() instead of Now(), because Now()-1 return current time-1 second, not previous day.  And for the sample you provided, you can test it by replace Today() with Date(2020,5,19) to check the average of 5/18

 

Measure Calculate(divide(sum([Time ]),sum([Sales]),0),filter('Table',[Date]= TODAY()-1))

 

 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@V-pazhen-msft Thanks! This worked out perfectly!!!!

amitchandak
Super User
Super User

@nosaj03 , To avoid divide by 0 use divide . which you have done correctly

Calculate(divide(sum(Time),sum(Sales),0),filter(Table,[date] = Today()-1))

This might not work if date is selected then you have to use all
Calculate(divide(sum(Time),sum(Sales),0),filter(all(Table),[date] = Today()-1))

Better to use a date table


Last Day Non Continous = CALCULATE(divide(sum(Time),sum(Sales),0),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
Day behind Sales = CALCULATE(divide(sum(Time),sum(Sales),0),dateadd('Date'[Date],-1,Day))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

@amitchandak thank you for the insight. I have a date table that I created. 

 

When I use the formula you provided below 

 

Day behind Sales = CALCULATE(divide(sum(Time),sum(Sales),0),dateadd('Date'[Date],-1,Day))

 

It is still giving me the total based on the entire table and not the previous day. 

@nosaj03 , can you share the screenshot of the issue. This should have worked. Hope you are using date and any other date-related column from date table

@amitchandak using the exact formula you gave me the Avg Time for 5/18 should be 227

 

Its still giving me the AVG Time for both days of 264.

 

The date table I have uses Date just like in the formula but its giving me the average for all days and not the previous day

parry2k
Super User
Super User

@nosaj03 As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

 

and then you can use something like this

 

Previous Day SAles = 
CALCULATE ( SUM ( Table[SAles] ), DATEADD ( CalendarTable[Date], -1, DAY ) )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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