Adding Quantity of Late Items for Each Year Using DAX

[Anonymous]

Hello All,

I have been working on this DAX for a few days and am not getting the desired result, so figured I would try posting here for a change. Our IT department owns the data and I am unable to add columns within the tables themselves, and must get to what I need via DAX. Our organization deals with "certifications" which are annual income certifications for housing. Sometimes they are behind on recertifying, but our current data does not multiply the past due certification to tell us the quantity of certifications we are behind. I.e. if the last certification was due in March 2020, and was not completed, we are actually missing certifications for March 2020, March 2021, March 2022, and March 2023 - a quantity of 4. But our current system says we only have 1 late certification, which is what I am trying to adjust with this measure. 

I am trying to use DAX to add a "multiplier" (i.e. add quantity of late items for each additional year) to the current quantity based on the year of the "Next Certification" and what date I select for the report to be "as of" (Slicer Date). It seems I'm close, but whenever the current quantity is "2" (i.e. if it is registering that we had 2 late certifications due in 2020), my measure seems to be adding 2 for each additional year, and is giving me 8 instead of 5 for the late certs with multiplier. Even if our late quantity starts as 2, I only want the measure to add 1 certification for each additional year. 

Table Name: Certifications

Next Certification Reference Column: NextCertification as Date

Filter Reference Date: SlicerDate[SlicerDate]

Current (incorrect) column with # of late certifications: LateCertificationsNeverFinished

Here's what I have for the DAX currently:

Total Late Certs with Multiplier = if(hasonefilter(Certifications[NextCertification]),

calculate(if([NextCertification as Date]<SlicerDate[SlicerDate],

if(DATEDIFF([NextCertification as Date],SlicerDate[SlicerDate],DAY)>365,

value([LateCertificationsNeverFinished])+(ROUNDDOWN((DATEDIFF([NextCertification as Date],SlicerDate[SlicerDate],DAY)/365),0)),

value([LateCertificationsNeverFinished])),

value([LateCertificationsNeverFinished])),

filter(values(Certifications),Certifications[FamilyIsCurrent]<>0)),

sumx(filter(certifications,[LateCertificationsNeverFinished]>0 && Certifications[FamilyIsCurrent]<>0),

if([NextCertification as Date]<SlicerDate[SlicerDate],

if(DATEDIFF([NextCertification as Date],SlicerDate[SlicerDate],DAY)>365,

value([LateCertificationsNeverFinished])+(ROUNDDOWN((DATEDIFF([NextCertification as Date],SlicerDate[SlicerDate],DAY)/365),0)),

value([LateCertificationsNeverFinished])),

value([LateCertificationsNeverFinished]))))

Thank you for any help!

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...