Forum Discussion
Calculating daily, weekly, monthly changes
Hello,
*I have attached a dummy file to explan the structure of my data, I will explain its components and other related info below*
| DateCalc | Classification | Count |
| 1/2/2023 | Sixth | 21 |
| 1/2/2023 | Seventh | 26 |
| 1/2/2023 | Eight | 28 |
| 1/3/2023 | Sixth | 22 |
| 1/3/2023 | Seventh | 27 |
| 1/3/2023 | Eight | 29 |
| 1/4/2023 | Sixth | 23 |
| 1/4/2023 | Seventh | 25 |
| 1/4/2023 | Eight | 30 |
| 1/5/2023 | Sixth | 21 |
| 1/5/2023 | Seventh | 26 |
| 1/5/2023 | Eight | 28 |
| 1/6/2023 | Sixth | 26 |
| 1/6/2023 | Seventh | 29 |
| 1/6/2023 | Eight | 32 |
| 1/9/2023 | Sixth | 27 |
| 1/9/2023 | Seventh | 32 |
| 1/9/2023 | Eight | 35 |
| 1/10/2023 | Sixth | 31 |
| 1/10/2023 | Seventh | 34 |
| 1/10/2023 | Eight | 39 |
I am counting daily reads to understand growth for a certain location. Each WEEKDAY (weekends are not read), an attendance count is pulled in and divided into gradeschool classification (6th-8th graders). For this report, I would like to be able to count the total attendance (all grades) per day and then show the growth by week, and then month and year of course (I believe if I can get the week then the rest should be pretty straightforward).
I was able to work out a measure that worked that simply found the most recent date, subtracted that date by 7, and then measured the % difference of the two values. Below is the formula.
The issue is that when I try to apply this to different ranges (last 30 days, 90 days, 180 days), it works out sometimes where -180 days ends up being a Saturday or Sunday and my % shows as Infinity. I have a DimDate table that has Weekday and other columns where this could probably be utilized. Maybe something along the lines of find most recent day and if is not weekday then go back another day until there is a value again.
This is really slowing me down and any help would be appreciated.
Thanks!
1 Reply
- foodd
Community Champion
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.
