plottting category wise running count on stack chart and want to carry forward the previous value

[sankalp_soni]

Hi , 

I am facing an issue while plotting the category-wise running count on stack bar graph with date. let me brief you about my table.

column names=dt_created, broker(non numerical values) 

below table is example:

dt_created            broker

27/07/2023             ab

27/07/2023             xy

27/07/2023             mn

28/07/2023             ab 

28/07/2023             xy 

28/07/2023             mn

01/08/2023             mn 

05/08/2023             xy

08/08/2023             xy

in the dt_created column, I have dates from 4 December 2020 to 8 August 2023 and in the broker column, I have the names of some brokers.

i am plotting the stack bar graph of the cumulative count of brokers and on x -axis i have put dt_created column with year and month hierarchy only and in legends also i have put broker. and i  have used the DAX function to calculate the measure of cumulative count:

broker cumulative = IF(COUNTROWS('table')>0,CALCULATE (

    COUNT ( 'table'[broker] ),

    FILTER(

        ALLEXCEPT('table','table'[broker]),

        'powerbi_users table'[dt_created] <= MAX('table'[dt_created] )

    )

),BLANK())

but the problem is that for "ab" broker i don't have any rows in August 2023,so in the stack chart it is not carrying forward the July month value. can anyone please help me with this? 

[sankalp_soni]

Hi @v-xinruzhu-msft @Ashish_Mathur ,

Thanks for your timely response. May be I couldn't make my problem perfectly clear. the sample table that I have shown you have only data for one year but in my actual table data ranges from December 2020 to august 2023. and now when i am using the methods provided it is giving me yearly cumulative and resetting that cumulative after one year but i want the continuation of that. 

let me add another table to clear my problem

dt_created            broker

04/12/2020             ab

01/01/2021              xy

05/07/2021              mn

19/12/2021              ab

21/01/2022               xy

26/10/2022              ab

27/07/2023             ab

27/07/2023             xy

27/07/2023             mn

28/07/2023             ab 

28/07/2023             xy 

28/07/2023             mn

01/08/2023             mn 

05/08/2023             xy

08/08/2023             xy

now from above table i want the cumulative graph from December 2020 to august 2023 and all the values should be carry forward.

Thanks in advance.

[Ashish_Mathur]

Hi,

Please find attached the PBI file.

[v-xinruzhu-msft · Accepted solution]

Hi @sankalp_soni 

Just modify the measure I have offered

Measure = COUNTROWS(FILTER(ALLSELECTED('Table'),[Column2] in VALUES('Table 2'[Column2])&&EOMONTH([Column1],0)<=EOMONTH(DATE(SELECTEDVALUE('Table 2'[Year]),SELECTEDVALUE('Table 2'[MonthNo]),1),0)))

Output

est Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sankalp_soni]

thank you so much, this worked for me.

But is there any way to show the months as the first three letters instead of numbers?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-xinruzhu-msft]

Hi @sankalp_soni 

Based on the information you have offered, you can refer to the following solution.

Sample data:

1.Create a new table first

Table 2 =
VAR a =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[Column1].[Year],
        'Table'[Column1].[MonthNo]
    )
VAR b =
    SUMMARIZE ( ALLSELECTED ( 'Table' ), [Column2] )
VAR c =
    GENERATE ( a, b )
RETURN
    SUMMARIZE ( c, [Column1].[Year], [Column1].[MonthNo], 'Table'[Column2] )

2.Then create a meaure

Measure = COUNTROWS(FILTER(ALLSELECTED('Table'),[Column2] in VALUES('Table 2'[Column2])&&[Column1].[Year]=SELECTEDVALUE('Table 2'[Year])&&[Column1].[MonthNo]<=SELECTEDVALUE('Table 2'[MonthNo])))

3.Then put the fields of table and measure to the visual

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.