cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zax
Frequent Visitor

DAX Count occurrences by month on semi-additive table

Hi all,

 

I'm having an issue that I think should be easy and I have searched but I don't know how to word it actually.

 

I have a 'Date' table and a 'Loan Balances' (fact) table joined on processing date. The fact table has two granularities, the last 60 days snapshot of daily loan balances (excluding weekends and holidays) and after 60 days it is rolled up to month ends only. 

 

What I'm trying to do is for the last 24 months, count how many loans were past due. For each month that has a loan past due count that as 1 (it was past due SOMETIME in that month). So if a single loan was past due for the last 12 months, I should have a count of 12. So when all loans are considered I need to count the occurrences and sum them up to get me the total count of loans past due in the last 24 months.

 

Below is a snippet of what I'm trying to achieve. 

There are two loans across in the sample, one that spans across historical month end data and into the daily data. the Yellow column would be what I want the measure to output. 

zax_0-1631709838365.png

 

This gets me close but obviously adds the daily data within the month when it should only count once.

SUMX(
   DISTINCT(Loans[AccountLoanKey]),
   CALCULATE(
        COUNT(Loans[DPD 30 to 59])
    )
)  

 

I think I need to use SUMX or COUNTX, but with the daily vs end of the month data I'm not sure best approach here and I might be overthinking it.

 

Any help would be apperciated!

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@zax ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Also, refer

https://www.sqlbi.com/articles/semi-additive-measures-in-dax/

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
zax
Frequent Visitor

@amitchandak , I included a snippet of what I'm trying to do. I hope this make sense and works?

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors