Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I wanted calculate a cummulative view.
My table looks like below,
Loan ID | Date | Status |
1 | 2-Jan | Contracted |
1 | 3-Jan | PTD |
2 | 4-Jan | Contracted |
3 | 5-Jan | Contracted |
4 | 1-Feb | Contracted |
5 | 1-Feb | Contracted |
5 | 2-Feb | PTD |
2 | 1-Mar | PTD |
6 | 2-Mar | Contracted |
4 | 3-Mar | PTD |
I wanted to calculate how many loans are there in Contracted and PTD status as of Jan, feb and march.
my table should look like this,
Contracted count | PTD Count | |
Jan | 2 | 1 |
feb | 3 | 2 |
march | 2 | 4 |
As of January, There are 3 loans, but for loan id 2 and 3, the last stage was Contracted for that month but for loan ID 1 the last stage is PTD. Therefore, the Contracted Count is 2 and PTD count is 1.
As of February, There are two new loans 4 and 5, the contracted count will 3 since loan id 2 and 3 from the previous month is still in Contracted and loan id 4 in feb is also in Contracted. Therefore the count is 3.
Similarly for PTD, loan id 1 from jan month and loan id 5 from feb is still in PTD. Therefore, the PTD Count is 2.
As of March, There is one new loan id 6, the contracted count will be 2 since Loan ID 3 and 6 still remains in contracted (Loan id 2 and 4 has moved to next stage PTD so it is excluded). Similarly for PTD, the count is 4 ( loan id 1 from jan, loan id 5 from feb and loan id 2 and 4 in march).
How do i calculate this?
my visual should look like
Please Help!!
Thanks,
Dharani
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Sir,
This works fine when it is applied to this smaller dataset. but when i apply it to my larger dataset (which is something similar with lot more statuses), the measure 2 and 3 which you have calculated comes to be empty. Please give me any leads where it would have gone wrong.
Thanks,
Dharani
If it working on a small dataset, it should also work on a large one. I cannot guess the mistake which you are commiting.
Am not sure what is the issue Sir.
I have put my file here and followed the exact procedure.
If possible, can you take a look and guide. It would be of great help.
Here is the link
https://drive.google.com/drive/folders/1kUJEzxtkH40lzd3h4FvOuysf9JPo8_FS?usp=sharing
Thanks
Hi,
You may download my PBI file from here.
Hope this helps.
Hello Sir,
Sorry for the trouble!
In the above corrected file you have attached, i have tested with a pat_id 9936 for the year 2022 like below,
The dataset for this pat_id,
For the september month there are 4 event_names and the most recent even is the full loan amount considering the timestamp. So the expected output for september and october should reflect only full loan amount and
similarly for november and december it should be Payment processed( since the most recent event_name is payment processed considering the time stamp).
But we could see all the status since we considered only the date in the calendar function.
Is it possible to change this with the timestamp?
It could be of great help if you could give me an insight!
Thanks
Hi,
Share the dowload link of the PBI file and show the problem there. Show the expected result as well.
Hi Sir,
Thank you so much!
You can download the file below,
https://drive.google.com/drive/folders/1kUJEzxtkH40lzd3h4FvOuysf9JPo8_FS?usp=sharing
It is the file you have shared me prior with filter applied on the visual for a particular pat_id 9936 for year 2022.
The dataset for this pat_id is below,
Expected output:
For the september month there are 4 event_names and the most recent event is the full loan amount considering the timestamp. So the expected output for september and october should reflect only full loan amount (the graph for should show only Full Loan Amount Event and should not show the other 3 event_names like Application Link Sent, Application Complete and Approved) and
similarly for november and december it should be Payment processed( since the most recent event_name is payment processed and begin treatment considering the time stamp and should not show the event_name Payment Pending).
Please Help me with this.
Thanks,
Dharani
Hi,
Someone else will help you with this.
Thank you so much Sir!!
One final question,
In your last measure,
Hi,
Does this work?
Measure 2 = SUMX(CALCULATETABLE(VALUES(Data[PAT_ID]), DATESBETWEEN('Calendar'[Date], MINX(ALL('Calendar'), 'Calendar'[Date]), MAX('Calendar'[Date])),data[Flag]=1),[Measure 3])
Ensure that the entries in the Flag column are of the Number data type.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
106 | |
77 | |
66 | |
65 |