Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table of accounts similar to this:
Account ID | Date Opened | Date Closed | Status | ProductTypeID |
1500311D | 28/07/2015 | 16/01/2019 | 3 | C |
716032 | 22/06/2017 | 25/02/2019 | 3 | C |
Z1000 | 1/04/2008 | 31/12/2100 | 1 | C |
I'm attempting to calculate a graph of open accounts over time. DimDate is my calendar table, with all dates from 1/1/2016 to now. My slicer for DimDate is on the page. My formula for number of open accounts is:
# Loans Open = CALCULATE( DISTINCTCOUNT(DimAccount[AccountID]), FILTER( DimAccount, DimAccount[DateOpened] <= LASTDATE(DimDate[Date]) && OR(DimAccount[DateClosed] >= LASTDATE(DimDate[Date]), DimAccount[status] = 1) && DimAccount[ProductTypeId] <> "D" ) )
In this example my measure would return 2 with the date slicer set to 4 April 2018. The account "Z1000" is not recognised even though it is open on 4 April 2018. Any clues?
The date table (DimDate) has no direct relationship to the DimAccount table either through closing or opening date. There are some indirect relationships e.g Each account has a number of AccountLogs, each AccountLog has a Date which is related to the DimDate table.
Hey @pistachio
It's a bit hard (for me) to assist as you are missing the DimDate table.
How does it look like?
What is DimAccount[Pk]?
Please update your question with the correct details.
Cheers!
A
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |