Forum Discussion
Counting value changes in given time period
Hi everyone,
I am pretty new to PowerBI, and need some help. Here is some information regarding the problem that I am unsure how to approach.
Data Source: SharePoint Online List
What do I want to achieve: To count (+/-) that the value has changed in the last 7 days. (i.e. In the left column I currently have 51 requests, and last week I had 42 requests - in the right-hand column I want to show that I have had +9 requests)
I was able to do the initial total requests by just using the created date and filtering by in the last 7 days but I am unsure how to approach the following ones.
Each request moves through 7 phases, and what I want to communicate is how the value has changed within each phase over the course of the last week. I do not have a date field in my SharePoint list for recording each change in the status.
I would also like to communicate this with positive and negative signs (+/-) before the number.
Please see a mockup here:
Thanks in advance!
Hi Anonymous
In the absence of your sample data, maybe you can measure like:
Measure = var this_week= CALCULATE( SUM([values]), FILTER( 'Table', 'Table'[Date]=thisweek // return date of this week )) var Last_week= CALCULATE( SUM([values]), FILTER( 'Table', 'Table'[Date]=thisweeek-7 // return date of last week )) return IF( this_week-Last_week>0, CONCATENATE("+",this_week-Last_week), this_week-Last_week )Best Regards,
Link
4 Replies
Anonymous , not very clear. For WOW refer
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Hi Anonymous,
Sorry, i'm not sure about your expected output.
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Best Regards,
Link
The expected output is to have a card (like in the mockup picture) that counts the amount the value has changed in the last 7 days. Essentially I want to count what the value was 7 days ago and count what the value is today, and then show what the difference is. (i.e. last week I had 42 requests, this week I had 51, so the output would be +9, if I had 42 requests last week, and only 40 requests this week, the output would be -2) Does that help clarify?
In regard to data that could be relevant for this, I only have the date the request was created and which status it is currently in (the seven statuses are shown in the mockup).
Thanks!
Hi Anonymous
In the absence of your sample data, maybe you can measure like:
Measure = var this_week= CALCULATE( SUM([values]), FILTER( 'Table', 'Table'[Date]=thisweek // return date of this week )) var Last_week= CALCULATE( SUM([values]), FILTER( 'Table', 'Table'[Date]=thisweeek-7 // return date of last week )) return IF( this_week-Last_week>0, CONCATENATE("+",this_week-Last_week), this_week-Last_week )Best Regards,
Link
