Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
water-guy-5
Helper III
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*

DateCalcClassificationCount
1/2/2023Sixth21
1/2/2023Seventh26
1/2/2023Eight28
1/3/2023Sixth22
1/3/2023Seventh27
1/3/2023Eight29
1/4/2023Sixth23
1/4/2023Seventh25
1/4/2023Eight30
1/5/2023Sixth21
1/5/2023Seventh26
1/5/2023Eight28
1/6/2023Sixth26
1/6/2023Seventh29
1/6/2023Eight32
1/9/2023Sixth27
1/9/2023Seventh32
1/9/2023Eight35
1/10/2023Sixth31
1/10/2023Seventh34
1/10/2023Eight39




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!
1 REPLY 1
foodd
Super User
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.

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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