Measure or calculated column for daily , weekly and avg usage

[Nari1998]

I have a table in power bi which is a sharepoint list containing info of encodian licenses which contains the following 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 steps per app is available on each day), Datum (which contains the date on which all the previous information is registered/logged).

I now want you to create a calculated column or measure (hoewever i don't know which is best for my case) which calculates the daily usage by looking at the Available , App Name and Datum column.

SO for example:

App Name                                        Available                      Datum                        Daily Usage

Test 1                                                300                              10 jan 2023                 Test 1 = 20 (300-280) and Test 2 = 20 (420-400) Test 2                                                420                              10 jan 2023               

Test 1                                                280                              11 jan 2023

Test 2                                                400                              11 jan 2023     

So the total steps used when looking at the "Available" column for App Name Test 1 = 20 and same goes for Test 2. So it is subtracting the values from the "Available" column based on the previous date - minus current date and looking at the App Name. Can someone help me with the DAX calculation? I'm new to it and seems quite difficult for now.

Your help would be appreciated...thnx!

[Nari1998]

Hi @v-yilong-msft ,

Thank you so much for the code!

I've been struggling with it for a few days now.

I have applied it and works, but for the year of 2024 it gave a negative va;ue somewhere (so what i forgot to mention was that every month the available steps are added again ( so for example on 1stf feb 2024 500  or 1200 steps are added based on its subscription level). So on 31st 2024 there were 251 available and on 1st feb 2024 they were now 500 and then it gives me faily usage of -249. 

So it should count dailu usages but on the switch from last day previous month to new day of new month it shouldnt subtract but start count new for that mount , so when nothing is used it should show 0....any tips on how to tackle this?

Also could you maybe also help with measure of 3 days, weekly and monthly usage? Is measure better or calculated column?

[v-yilong-msft]

Hi @Nari1998 ,

Can you provide me with more details with your desired output and pbix file without privacy information (or some sample data)?

How to Get Your Question Answered Quickly 

Best Regards

Yilong Zhou

[Nari1998]

Hi @v-yilong-msft thank you for your reply...i'm afraid i won't have the time to create the sample data pbix file.

What i can tell you is the following:

Our consultancy company does reselling of Encodian licenses (which have a amount of steps available per month for example the the Midsize subscription gets 1200 available monthly steps and the standard gets 500 monthly). These licenses are used with certain apps by which throughout the month the steps decrease. We now want to monitor that over all our clients and apps they use which make use of these encodian licenses. We want to know the daily usage, 3 day usage, weekly , monthly and average usage. We will use this to i think visualize with a trend line axis to forecast and see if before the end of the month the amount of available steps per app and its encodian license will be decreased and when or if we should tell our clients to purchase additional licenses.

Note: every 1st day of the new month all remaining available steps are cleared and then based on its subscription level the steps are added. 

So the problem i had with the daily usage is that if on 31st jan i had 15 available steps and on 1st fb i get 500 is then shows -485, hoewever i just want to show 0 because it should start counting daily usage from the 2nd of feb and not on 1st feb by sutracting previous day with that day.

I hope you'r able to understand/

Do you maybe have any other suggestions or tips on how to best tackle this?

[v-yilong-msft]

Hi @Nari1998 ,

Here are my answers to your questions.

Firstly, I create a table as you mentioned.

Create a new column and enter the appropriate DAX code.

Daily Usage =
VAR CurrentDate = 'Table'[Datum]
VAR PreviousDate =
    CALCULATE (
        MAX ( 'Table'[Datum] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[App Name] = EARLIER ( 'Table'[App Name] )
                && 'Table'[Datum] < CurrentDate
        )
    )
RETURN
    IF (
        ISBLANK ( PreviousDate ),
        BLANK (),
        CALCULATE (
            MAX ( 'Table'[Available] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[App Name] = EARLIER ( 'Table'[App Name] )
                    && 'Table'[Datum] = PreviousDate
            )
        ) - 'Table'[Available]
    )

This will give you the results you need.

[Nari1998]

Hi @v-yilong-msft can you help me again but this time with measur of dynamic weekly usage and dynamic average weekly usage? 

Im not good with DAX yet, and my boss wants me to calculate three day, weekly and monthly usage aswell as its average dynamically. And based on this we want to see how much is left when used within a month. 

Please help😓

[Nari1998]

hi @v-yilong-msft could you now help me with a measure that calculates the usage over 3 days? i can that use that to create the usage over a week and month. Please help out, im really weak at dax