Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Anonymous
Not applicable

Cummulative Trending View

Hi,

 

I wanted calculate a cummulative view.

My table looks like below,

Loan IDDateStatus
12-JanContracted
13-JanPTD
24-JanContracted
35-JanContracted
41-FebContracted
51-FebContracted
52-FebPTD
21-MarPTD
62-MarContracted
43-MarPTD

 

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
Jan21
feb32
march24

 

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

dharanisrees_0-1675535603292.png

 

Please Help!!

@Ashish_Mathur 

 

Thanks, 

Dharani

 

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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,

 

dharanisrees_0-1676984632132.png

 

The dataset for this pat_id,

 

dharanisrees_1-1676984704956.png

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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. 

 

dharanisrees_1-1677237132377.png

The dataset for this pat_id is below,

 

dharanisrees_0-1677237093356.png

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.