Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have a list of accounts with opening and closing dates. Now I need a report that shows the number of open accounts over the month (Year to date, Month to date). The accounts are counted up to the month in which they are closed. I've tried with a calendar table and witgout a relationship to the accounttable, this doesn't work.
Solved! Go to Solution.
@hmo , refer to my HR blog for same. Seem like similar problem
Check - https://youtu.be/e6Y-l_JtCq4
@hmo , Join both of them with date table one will active another will be inactive assume the closed_on is inactive
Try measures like with date tbale and time intelligence
YTD Open = CALCULATE(Count(Table[Account]),DATESYTD('Date'[Date],"12/31")) // date creation is active join
YTD closed = CALCULATE(CALCULATE(Count(Table[Account]), userelationship('Date'[Date], Table[Closed_on]), not(isblank(Table[Closed_on]))),DATESYTD('Date'[Date],"12/31"))
Refer why Time intellignce fails : https://youtu.be/OBf0rjpp5Hw
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
@amitchandak I think this is not the solution table below should end in chart below. We need to count all open accounts by month in the given time period.
Account;date_of_creation;closed_on
1;2019-12-10;2019-12-11
2;2019-12-10;2020-01-02
3;2019-12-10;
4;2019-12-10;2020-02-25
5;2019-12-10;2020-05-13
6;2020-01-03;2020-03-14
7;2020-03-11;2020-05-11
@hmo , refer to my HR blog for same. Seem like similar problem
Check - https://youtu.be/e6Y-l_JtCq4
@amitchandak Thank you very much. I think this formula in your post leads me to the right results:
Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
70 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
128 | |
29 | |
28 | |
24 | |
22 |