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.

[Nari1998]

Thank you @quantumudit for the help! .

Are these dynamic or static measures?

Also these measures 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?

[quantumudit]

No, the weekly average measure tells you about the average "total available" value in the last 7 days (from the current date in the context). It doesn't subtract itself from any number to show you a remainder kind of value. Please let me know in detail what exactly you want to achieve with an example.

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[Nari1998]

So the thing is one every first day of the new month an amount of steps/acions per app (based on their subscription level) is given it can be 1200 0r 500. Everyday the amount decreases because it is used. So if yesterday i had 300 steps for App 1 and today have 290 steps  the usage is 10. And so i want to calculate the usage over 1 day, 3 day and week as well as the average weekly usage within the month. This because we want to know how much is used and how much we still might have left during the month in order for us to know if we need to purchase addiotianl steps or not for this month. Because it keeps decreasing, so before the amount of available steps becomes 0 we need to know when to purchase additional. I hope im a bit clearer now, if not , let me know!

And thanks for the help

[quantumudit]

Hi @Nari1998 

In that case, there should be a column "used" to get how many steps are used from the "Available". In the dataset screenshot, I can only see the "Available" column, so the calculations are done accordingly... It seems that the desired result requires an additional column in the dataset.

Kindly correct me if I am mistaken somewhere in understanding the problem.

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[Nari1998]

Yes it should be based on additional Used column, i have one that calculates the daily usage only. My apologies for not mentioning it earlier!

[quantumudit]

If thats the case, then could you attach a sample dataset and send it? so that it would be easier to calculate and understand the requirements better.

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[Nari1998]

I wasn't able to attach the file, because it gave me error, so here is a link to a folder which contains the sample dataset in excel and .csv format: https://drive.google.com/drive/folders/1to-uWXUxMKHeFlOTTEkShUI2ZUHepZ4f?usp=sharing

[quantumudit]

Great. Thanks for the dataset. Let me check & do the maths and get back to you.

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[quantumudit]

You can create the DAX calculations as follows:

A measure for total available or, daily available value:

Total Available = SUM(Apps[Available])

A measure for the 3-day running summation:

3-day Running Total = 
CALCULATE(
    [Total Available],
    DATESINPERIOD(Apps[Datum], MAX(Apps[Datum]), -3, DAY)
)

A measure for the 7-day (weekly) running total:

Weekly Running Total = 
CALCULATE(
    [Total Available],
    DATESINPERIOD(Apps[Datum], MAX(Apps[Datum]), -7, DAY)
)

A measure for the 7-day (weekly) moving average:

Avg Weekly Total = 
AVERAGEX(
    DATESINPERIOD(Apps[Datum], MAX(Apps[Datum]), -7, DAY),
    [Total Available]
)

A measure for the monthly running total:

Monthly Running Total = 
CALCULATE(
    [Total Available],
    DATESINPERIOD(Apps[Datum], MAX(Apps[Datum]), -1, MONTH)
)

Here is the snapshot of an example table

I hope this will help. Please let us know in case of anything. Also, you can leverage the DAX patterns in the provided calculation if you need to calculate alternatives like monthly average, 3-day average, etc.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree