cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Cummulative Trending View

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

Thanks,

Dharani

11 REPLIES 11
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

Super User

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

Thanks

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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,

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

Super User

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
Anonymous
Not applicable

Hi Sir,

Thank you so much!

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).

Thanks,

Dharani

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Thank you so much Sir!!

One final question,

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.
Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors