View My Drafts
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.I have a Date table and a license table. The license table has several fields with the following columns;createdon - Dateexpirationdate - DateproductID - Sting - Unique IDServiceType - String - Support TypeCASPService - Calculated Column with a 1 or 0 depending on the matched service typeThe dax i use to count the licenses that match the right service typeCASPCount = 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.
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 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])
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.
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