Daily count of active licenses

[Anonymous]

Hello,

I'm runnning into an issue where I get the same numbers each day regardless of change and it doesn't seem to be keeping the daily unique values.  You can see below that when the count for 7/22/2021 is 3375, but when the data is refreshed and the new day, 7/23/2021 is added, it changes all the counts to the value of today instead of keeping the earlier values.

 

I have a Date table and a products table.  The products table has several fields, but primarily, 2 date fields, a license id field, and a service type field.  The date fields are 'created on' and 'expiration date'.  The service type field is a subgroup field that identifies a different program set.  I need to be able to count active customer license by service type each day for another calculation incidents per customer average within a limited group.

I have completed a crossjoin on my date table and my products table which is getting me closer to what i need.  The image below is first, my date table, then my products table followed on the right with my joined table.

DateKey = date table

cv_commcells = product table

CommCellCount = joined table

formula for crossjoin

CommCellCount = CROSSJOIN('DateKey', cv_commcell)

formula for license count

CASPCount = If('CommCellCount'[CASPService]=1, If('CommCellCount'[cv_expirationdate]>='CommCellCount'[Date], 1, 0), 0)

I've also tried this formula for licence count, but i get identical results....

CASPCount_2 = CALCULATE(SUM(CommCellCount[CASPService]), Filter(CommCellCount, 'CommCellCount'[cv_expirationdate]>='CommCellCount'[Date].[Date]))

What i have found is that if i do the following, i get the daily unique count, but not for the individual service type.

CASPCount = If('CommCellCount'[cv_expirationdate]>='CommCellCount'[Date], 1, 0)

My relationship setup is here as well with no active or inactive relationships.

Any help would be appreciated.

[Anonymous]

I'm still stuck. 

I'm trying to collect the number of active licenses per day.  An active license will have expiration date that is a future date, while expired licenses will have an expiration date with a date of yesterday or older.

I looked at the Related() function, but i'm not doing a lookup as much as a daily count of active licenses.

At this point, i have several calculations that rely on the number of active licenses per day, but until i get this straight, i'm stuck.

What i have found is that if i do the following, i get the daily unique count, but not for the individual service type.

CASPCount = If('CommCellCount'[cv_expirationdate]>='CommCellCount'[Date], 1, 0)

Why do I lose the daily count when filtering out by service type?

CASPCount = If('CommCellCount'[CASPService]=1, If('CommCellCount'[cv_expirationdate]>='CommCellCount'[Date], 1, 0), 0)

Any thoughts?  I'm struggling why this is so difficult to get straight.

[v-yalanwu-msft]

Hi, @Anonymous ;

Based on the similar data you provided, I tested it, because you joined two tables with CROSSJOIN (), cv_commcell has 3 rows, DateKey has 10 rows, and CROSSJOIN () has 30 rows,According to  Expiration date>='CommCellCount'[date], Because they might have the same count by coincidence, just like I have a lot of 2  in my data.

If the two tables are in a one-to-many relationship, try RELATED()，So what exactly do you want the output to be？

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks.  I understand some of the dates may be duplicated due to no licenses expiring on certain days causing a duplicate count for that day, but what i'm finding is that every day the refresh happens and a new day is added, the count changes for all past days. 

As you can see from today after the refresh, all past dates now show the same count, while it is correct for today, it's not correct for yesterday or the day before....

I have created a relationship and that didn't help.

I have tried the following as you can see under CASPCount_4 and i get a very large number that also does not change from day to day.

CASPCount_3 = CALCULATE(SUM(CommCellCount[CASPService]), Filter(ALLEXCEPT(CommCellCount,CommCellCount[CASPService]), 'CommCellCount'[cv_expirationdate]>='CommCellCount'[Date].[Date]))

I also looked at Related(), but I'm unsure how that works as i'm not doing a lookup but a count or sum.

I'm not sure where to go next.

[Anonymous]

I'm trying to collect the number of active licenses per day.  An active license will have expiration date that is a future date, while expired licenses will have a expiration date will have a past date of yesterday or older.

At this point, i have several calculations that rely on the number of active licenses per day, but until i get this straight, i'm stuck.

[lbendlin]

Make sure the Date column in your visuals comes from the Datekey table, not from any of the fact tables.

[Anonymous]

It does.  You can see on the right of the highlighted visual.  I also confirmed i have it marked as a date table.

[v-yalanwu-msft]

Hi, @Anonymous ；

If you want calculate sum by every service type, you could use ALLEXCEPT(), and try :

CASPCount_3 = CALCULATE(SUM(CommCellCount[CASPService]), Filter(ALLEXCEPT(CommCellCount,CommCellCount[CASPService]), 'CommCellCount'[cv_expirationdate]>='CommCellCount'[Date].[Date]))

Besides, I don't know much about your date. For example, CV_EXPIRationdate is 2000, and date is 2019.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous  Just expanding on what @lbendlin mentioned, you need to set a relationship between the date table with transaction tables, and in visuals, use date from the date table, and then you can view count from the respective tables by period.

You need to understand the basics of Power BI (if you haven't chance to look at it yet) and it is a good start. Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[lbendlin]

a repeating number often means that you are combining data from tables that have no relationship.  Your data model screenshot confirms that. Rethink your data model, and add the required connections.

[Anonymous]

Thanks.

I initially had a relationship setup between the date table and the cv_expirationdate on my licensing table. but I still had the issue with every update in my licensing count changed my count for every day.

The closest i've been able to get is with the link below which my data resembles. 

https://community.powerbi.com/t5/Desktop/Count-number-of-products-over-time/td-p/540411

I've placed the relationship back in for DateKey -> CommCell count, 1 to many, date to date as well as the relationship for DateKey -> cv_commcell, 1 to many, date to cv_expirationdate...but no change.

Even updating CASPCount_3 with the dax from  v-yalanwu-msft still gives me the same value each day.

What i do find intersting is that there is a variance sporadically throught my measures as you can see below as the count changes from July 1st to June 30th then again on June 28th.