Cumulative Total Based on Month and Year

[Mayuresh_Joshi]

I am trying to build a bar chart that shows the count of rows for the month.

For example, in my table, if I have 10 rows for January dates, it should show the count as 10.

If I have 7 rows for February dates, it should show 17 as a count. How can I achieve this in Power BI? 

I have already followed the below articles with no success:

1) https://community.powerbi.com/t5/Desktop/DAX-Using-GROUPBY-to-get-a-simple-count-of-rows-in-groups/m...

2) https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115 

The problem in my case is that, I do not have any column to SUM the values. I need to count one row as one count. But when I try to create new measure or new column, it do not calculate the correct values.

Please find attached screenshots of the database and my graph. The red and green bars represent the count without cumulative values. I need those to be cumulative with previous month.

[v-yalanwu-msft · Accepted solution]

Hi, @Mayuresh_Joshi ;

I tested it , it's okey .Are you missing something?

cumulative = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),[Create Date]<=MAX('Table'[Create Date])))

Cumulative Count for 2022 = 
CALCULATE([cumulative], 'Table'[Create Date].[Year] IN { 2022 })

The final show;

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mayuresh_Joshi]

@danextian Thanks for the reply. I tried the way you explained in your reply. But it is still not showing the correct data.

Steps I followed: 

1) Created a measure:

Row Count = COUNTROWS (  Table  )

2) Created a measure:

Cumulative Count = 
CALCULATE (
    [Row Count],
    FILTER (
        ALL ( Table[Create Date] ),
        Table[Create Date] <= MAX ( Table[Create Date] )
    )
)

3) Created one more measure because I want to show only 2022 year data

Here KPI-Target is another table which has targets defined for each month

Cumulative Count for 2022 = 
CALCULATE([Cumulative Count], Table[Create Date].[Year] IN { 2022 })

4) Added Bar chart, X-Axis = month of creating data, Y-Axis = Cumulative Count for 2022

The graph is showing values as attached. I am expecting the graph values to be cumulative, but in this case it seems like it is only couting values for that month and not adding values from previous month.

I have also attached the sample data that I am using. (Note: I have removed some unwanted columns from the excel. I have also applied a few filters in the bar chart so the count is less (in your case if the count is more, you can ignore it)

https://docs.google.com/spreadsheets/d/1SztnlI4do47JVrSPL45rnb3Egiknvpdg/edit?usp=sharing&ouid=11431... 

This is how my chart is looking which is not correct, next month's value should always be greater than previous

[danextian]

Hi @Mayuresh_Joshi ,

For your use case, i would suggest to use a separate dates table. You can create one in DAX or in Power Query. 

Dates = 
VAR __BASE =
    CALENDAR ( MIN ( 'Table'[create_date] ), MAX ( 'Table'[create_date] ) )
RETURN
    ADDCOLUMNS (
        __BASE,
        "Year", YEAR ( [Date] ),
        "Month Short", FORMAT ( [Date], "mmm" ),
        "Month Number", MONTH ( [Date] )
    )

For cumulative count

Cumulative Count = 
CALCULATE (
    [Count],
    FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

YTD count

YTD Cumulative Count = 
TOTALYTD ( [Count], Dates[Date] )

sample output

PBIX - https://drive.google.com/file/d/1Lr0UiYgzGQ_itTM8Z2mfVumoV3ccU3jR/view?usp=sharing 

[danextian]

Hi @Mayuresh_Joshi ,

You can use COUNTROWS to count the number of rows in a table. Assuming  you don't have  separate facts and dates tables, these measures should work. Otherwise, please post a sample data that we can copy-paste.

Row Count =
COUNTROWS ( Table )

Cumulative Count =
CALCULATE (
    [Row Count],
    FILTER (
        ALL ( Table[Create Date] ),
        Table[Create Date] <= MAX ( Table[Create Date] )
    )
)