Calculating daily, weekly, monthly changes

[water-guy-5]

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!

[foodd]

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.

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.