Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
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?
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
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?
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.
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😓
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
17 | |
17 |
User | Count |
---|---|
33 | |
25 | |
18 | |
15 | |
13 |