Calculate cumulative string according to calendar date

[Anonymous]

hi All,

I'm having a problem to calculate cumulative string data. The data Table contains 'DOCUMENT' in string format and 'Planneddate'. and want to calculate how many document counted with respect to Calendar table to get an idea how many document counted to Calendar date. There's also some duplication of string expected in the column 'DOCUMENT'. I have seen some sample of cumulative calculation, should be simple but this doesn't work as expected. If someone can help on this.

cumulative_data_string 

cumulative count string data

Thank you

[Anonymous]

hi, it seems doesnt work still. 

The file is below for further check.
cumulative_data_string.pbix 

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

Hope this helps.

[Anonymous]

hi Ashish,

Thank you. this is what i'm looking for.
One thing i notice, the table Date now in visual shows only rows with value 'Documents till date'. Any idea how to show it like original table in Calendar 'Date' vs 'Documents till date'?

Thanks in advance 🙏.

[Ashish_Mathur]

You are welcome.  I cannot understand your new requirement.

[Anonymous]

hi Ashish,

for the coding, the value shown only for the dates that has count value. i'm trying to modified to include the whole calendar date that was generated previously. at the early date (Jan 1st), we dont have data means zero until we start having it at Aug 4th. 

i'm doing some modification to include all calendar date, but still stuck on generating count at dates with zero count but follow previous value. attached the figure and file.
cumulative_data_string_07082023.pbix 

doc till date include all calendar date.
Thank you in advance,

Yudhi

[Ashish_Mathur · Accepted solution]

PBI file attached.

[Anonymous]

Hi Ashish,

Perfect! this is what i'm looking for. Thanks for the support 👍.

Regards, 

Yudhi

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

That link take me to a sign-in page.  Try this method

1. Create a Calendar Table
2. Create a relationship (Many to One and Single) from the Date column of the Calendar Table to the Date column of the Calendar Table
3. To your visual, drag the Date field from the Calendar Table
4. Write these measures

Measure = distinctcounta('Table'[Document])

Measure1 = calculate([Measure],datesbetween(calendar[Date],minx(all(calendar),Calendar[date]),max(calendar[date])))

Hope this helps.