cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## 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.

Last 7 Days =
var a = LASTDATE(FactWaterActiveConn[DateCalc])
var b =
CALCULATE(
SUM(FactWaterActiveConn[Active_Count]),
FactWaterActiveConn[DateCalc] = a)
var c = a - 7
var d = CALCULATE(
SUM(FactWaterActiveConn[Active_Count]),
FactWaterActiveConn[DateCalc] = c)
Return
(b-d)/d

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!
Super User

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

Please show the expected outcome based on the sample data you provided.

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors