Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Daily Count of licenses in fact table

  • 1.  Daily Count of licenses in fact table

    Posted Aug 03, 2021 06:06 PM
    Hi Guys,

    I'm stumped and could really use some help.

    I'm running 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. I'm trying to count the number of active licenses each day.  An active license will have expiration date that is a future date from today forward, while expired licenses will have an expiration date with a date of yesterday or older.  Active and Expired license counts should change daily.

    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 , 3377, instead of only changing the value for 7/23.

    bobcat84_2-1627049236242.png
    bobcat84_3-1627049298137.png
    I have a Date table and a license table.  The license table has several fields with the following columns;

    createdon - Date
    expirationdate - Date
    productID - Sting - Unique ID
    ServiceType - String - Support Type
    CASPService - Calculated Column with a 1 or 0 depending on the matched service type

    The dax i use to count the licenses that match the right service type

    CASPCount = CALCULATE(SUM(license[CASPService]), Filter(license, license[expirationdate]>=Today()))

     

    My relationship is setup as a one to many between my date table, DateKey[Date] and license[expirationdate] on the license table.
    The DateKey table is marked as a date table

    Any help would be appreciated.

     



    ------------------------------
    Scott Arthur
    Program Manager
    ------------------------------


  • 2.  RE: Daily Count of licenses in fact table

    Posted Aug 03, 2021 09:28 PM

    This looks like a relationship issue to me.

    Createdon should join to calendar

    expiration date should be greater = the calendar date, not today.

    Alternatively you may need another calendar for Expiration Date or use Calculation groups.



    ------------------------------
    Philippe Addelia
    Director of Business Intelligence
    Upland CA
    ------------------------------



  • 3.  RE: Daily Count of licenses in fact table

    Posted Aug 03, 2021 10:11 PM
    Thanks Philippe, but I didn't see any change.

    I don't understand, why use the createdon date?  Once a license is created, the createdon date never changes.  When a user buys support or renews support for the license, it will have an expiration date of some date in the future. If support on the license has expired, then it will have an expirationdate of yesterday or older.  That's why the count by day is so important.  A license may have expired support today with some date in the past, but the user could renew support and tomorrow their expiration date is some date in the future.

    Based on this explanation, are you saying I should change my relationship to point to the createdon date instead of the expiration date?

    I just changed the relationship  from datekey[date] --> license[expirationdate] to a 1 to many from datekey[date]-->license[createdon] date.


    I changed today to datekey[Date], but it didn't make a difference.  It still changes the value for every day and not just today.  You can see July 30th is 3377, but after the update and refresh of data, July 30th is the same as August 6th, 3305.

    CASPCount = CALCULATE(SUM(license[CASPService]), Filter(license, license[expirationdate]>=Datekey[Date])



    ------------------------------
    Scott Arthur
    Program Manager
    ------------------------------



  • 4.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 04:17 AM
    Hi Scott
    I know this sounds obvious but shouldn't you be filtering the "cv_commcell" table, not the table called "license"?

    ------------------------------
    Alison Box
    Power Bi Consultant
    ------------------------------



  • 5.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 10:09 AM
    Hi Alison,

    It's the same table.  I was trying to keep the data somewhat private by using license instead of cv_commcell, but after i did the screen shot and pasted...i realized the mistake i'd made...


    ------------------------------
    Scott Arthur
    Program Manager
    ------------------------------



  • 6.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 09:51 AM
    Hey Scott,

    There issue is in your calculation. Your measure is always comparing the expiration date to Today(). You want to compare the expiration date to the date shown in the left hand column. So instead of always counting the licenses as of today, you will be counting the licenses as of the referenced date.

    Let me know if that helps.

    ------------------------------
    Evan Reid
    Data Analytics Manager
    ASHA
    ------------------------------



  • 7.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 10:12 AM

    Thanks Evan.

    Philippe made that comment last night and i changed the formula to the following referencing the date table instead of today(), but it still doesn't work.

    CASPCount = CALCULATE(SUM(license[CASPService]), Filter(license, license[expirationdate]>=Datekey[Date])



    ------------------------------
    Scott Arthur
    Program Manager
    ------------------------------



  • 8.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 10:17 AM
    Scott,

    What happens if you drop the join between the date table and your licenses table?

    ------------------------------
    Evan Reid
    Data Analytics Manager
    3012968651
    ------------------------------



  • 9.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 11:45 AM
    Evan,

    Same results.  It changes the value for every day.

    Below, formula, visual and relationship.

    Column = CALCULATE(SUM(cv_commcell[CASPService]), Filter(cv_commcell, cv_commcell[cv_expirationdate]>=Datekey[Date]))




    ------------------------------
    Scott Arthur
    Program Manager
    ------------------------------



  • 10.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 11:55 AM
    Scott,

    Are you building this as a column on the cv_commcell table, or as a measure?

    Give me a call if you have a second, 443-474-0500. We can get this working.

    ------------------------------
    Evan Reid
    Data Analytics Manager
    3012968651
    ------------------------------



  • 11.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 01:48 PM

    HI Evan,

    Thanks for the offer and I tried to reach out earlier.  I left a vm, but just in case, i can be reached at the following...740-475-2921.



    ------------------------------
    Scott Arthur
    Program Manager
    ------------------------------



  • 12.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 12:14 PM
    Try making this a measure in the fact table.

    ------------------------------
    Philippe Addelia
    Director of Business Intelligence
    Upland CA
    ------------------------------



  • 13.  RE: Daily Count of licenses in fact table

    Posted Aug 04, 2021 01:46 PM
    Hi Philippe, thanks again.

    I tried this as a measure, but nothing changes.  Below is the DAX for the CASPService column in the fact table.  We have about 8 different service types and i need the licenses that have CASP as the service type, then i just 1 or 0 the row.  All licenses that have a service type of CASP are active and not expired.

    I tried three different scenarios...

    • No relationship
    • 1 to many - date to expiration date
    • 1 to many - date to createdon date

    Below shows the measure in the fact table

    CASPService = IF(LEFT(cv_commcell[ServiceType],4)="CASP", 1,0)

    Measure = Sum(cv_commcell[CASPService])

    the visual below is before refresh with no relationship setup.

    ...And after...

    If I set the relationship to date-->cv_commcell[cv_expirationdate], I get the following before refresh.  All dates show 0 except for the very last row without a date and that shows 3392.


    After refresh


    The very last row shows 3319 without a date.


    And...with the relationship set from date-->cv_commcells[CreatedOn] as a 1 to many.

    Before Refresh...

    After Refresh



    ------------------------------
    Scott Arthur
    Program Manager
    ------------------------------



  • 14.  RE: Daily Count of licenses in fact table

    Posted Aug 05, 2021 04:21 AM
      |   view attached

    Hi Scott,

    Maybe I'm wrong, but I think that in your Filter function, the context of the relationship is still active. (Thus, when you look at 8/8/2021, it can't see 10/10/2021)

    I created quickly something similar (see attachment, and I get this result:

    Left table: shows per date how many licenses would be active

    Right table: the number of licenses that expire after that date 


    I've added my pbix file as attachment.
    Maybe it's not exactly the result, but hopefully it might get you closer to the solution.
    Kind regards,
    Johan


    ------------------------------
    Johan Vermeire
    IT Trainer
    Xylos nv
    0000000
    ------------------------------

    Attachment(s)

    pbix
    Example.pbix   48 KB 1 version


  • 15.  RE: Daily Count of licenses in fact table

    Posted Aug 05, 2021 03:41 PM
    Hi Johan,

    Thank you for the example.  I modified my formulas to mimic what you have and....it seemed to work....

    Active licenses (Daily) =
    VAR __RefDate = SELECTEDVALUE(DateKey[Date])
    return
    CALCULATE(
    COUNTROWS(cv_commcell),
    cv_commcell[cv_expirationdate]>=__RefDate,
    CROSSFILTER(DateKey[Date],cv_commcell[cv_expirationdate],None)
    )

    You can see below that each day had a individual count which does match the table.


    Until i refreshed the data.  After refreshing the data, all the numbers became the same value.


    relationship





    ------------------------------
    Scott Arthur
    Program Manager
    ------------------------------