Calculating three day, weekly and monthly usage aswell as average weekly usage

[Nari1998]

Above is a test table shown.

However the real table i have contains the followig columns App Name (which contains different app names), Subscription level (which contains the subscription level per app), Billing interval, Monthly actions (contains the value of how many monthly actions each app has based on its subscription level), Available (contains how many actions per app is available on each day), Datum (which contains the date on which all the previous information is registered/logged).

Note: On every first day of the new month an amount of actions are given to each app based on its subscription level and they decrease during the month when used over the days in the month. I now need to have the following calculations for daily usage, three day usage, weekly usage aswell as average weekly and monthly usage.

Im not great with DAX and would like to get some help on this please!

Thank you.

[v-rongtiep-msft]

Hi @Nari1998 ,

I see that you didn't reply when @Ritaf1983  asked you for data, so please try my formula first.

Daily Usage = 
VAR CurrentDayAvailable = MAX('Table'[Available])
VAR PreviousDayAvailable = CALCULATE(MAX('Table'[Available]), PREVIOUSDAY('Table'[Datum]))
RETURN PreviousDayAvailable - CurrentDayAvailable

Three Day Usage = 
VAR CurrentDayAvailable = MAX('Table'[Available])
VAR ThreeDaysAgoAvailable = CALCULATE(MAX('Table'[Available]), DATEADD('Table'[Datum], -3, DAY))
RETURN ThreeDaysAgoAvailable - CurrentDayAvailable

Weekly Usage = 
VAR CurrentDayAvailable = MAX('Table'[Available])
VAR SevenDaysAgoAvailable = CALCULATE(MAX('Table'[Available]), DATEADD('Table'[Datum], -7, DAY))
RETURN SevenDaysAgoAvailable - CurrentDayAvailable

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nari1998]

Hi @v-rongtiep-msft ,

Thank you for the help ( i was unable to create dummy data) , but i tried out with your calculations and got the following issues:

1. The one day measure doesn't calculate the usage correctly . If for example i have for the Test1 App on 13 april 419 actions/steps and on april 14 i have 417 then the usage of one day should show 2, but it is returning -498.

2. The 3 day and 7 day usage measures gives me the following error when placed in a card visual: 

Also are these dynamic or static measures?

They will be used to look  how much average remaining gets left within the month (maybe only the average weekly usage) so that we know if aditional steps/actions need to be purchased or not. So are these measures conform those requirements?

[Ritaf1983]

Hi @Nari1998 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523