Cummulative Trending View

[Anonymous]

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!!

@Ashish_Mathur 

Thanks, 

Dharani

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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

[Ashish_Mathur]

If it working on a small dataset, it should also work on a large one.  I cannot guess the mistake which you are commiting.

[Anonymous]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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

[Ashish_Mathur]

Hi,

Share the dowload link of the PBI file and show the problem there.  Show the expected result as well.

[Anonymous]

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

[Ashish_Mathur]

Hi,

Someone else will help you with this.

[Anonymous]

Thank you so much Sir!!

One final question,

In your last measure,

Measure 2 = SUMX(CALCULATETABLE(VALUES(Data[PAT_ID]), DATESBETWEEN('Calendar'[Date], MINX(ALL('Calendar'), 'Calendar'[Date]), MAX('Calendar'[Date]))),[Measure 3])

How do i add an another condition,

that i have to sumx the values of pat_id, only if a column called "flag" is 1.

[Ashish_Mathur]

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.