Cumulative Sum Table

[Syndicate_Admin]

Greetings colleagues,
I'm new to power BI, I'm doing the mundanza from excel to power bi and I ran into a problem.
I have data of this style

And I want to want a board or matrix with this style

I was thinking of just using some auxiliary table month.year and an id, but I still can't land the idea, even so I think it can be done easier, but I don't find how.


If you could support me, I would be infinitely grateful

[Syndicate_Admin · Accepted solution]

It seems to me that I have the solution, although there is always room for improvement, I share the file with you.

As I said at the beginning, I managed to do it with a row_number as an id, I still don't know if it's the best solotion, but it seems to funionize.

Thank you very much in advance for the help and I hope you can be helpful.

This type of table is very useful if you want to create time series with the maturation of different years.

I look forward to hearing from you.

Thank you very much in advance.

[DataNinja777]

Hi @Syndicate_Admin ,

I've prepared your required output for 2023 which includes 5 months of 2024, but putting 2024 month 01 to 05 side by side will require some effort, as it is duplicate information of periods 13 - 17 in 2023, and inbuilt Power BI relationship logic prevents duplicate counting by ensuring many to one relationship and do not recomment many to many relationship, presicely for this reason of preventing duplicate counting.  I attach pbix file for your reference.  

Best regards,

[DataNinja777]

Hi @Syndicate_Admin ,

The standard approach in Power BI is as provided by @Ashish_Mathur which involves creating a calendar table and leveraging the time intelligence function available in DAX. 

Regarding your example provided, I am wondering why your "mes" (month I suppose) is running up to 14 instead of 12. Is this intentional or it was not intended? If it runs up to 14 like posting period in SAP, you may need to create a summarized table of that to create one to many relationship with your fact table if you want to analyse by mes of up to 14 instead of 12.

Best regards,

[Syndicate_Admin]

It was placed intentionally, since what I want is to see "matured" data by year, so if I am going to analyze 3 years I would have, for 2022, I would have (12+12+5) periods, for 2023 (12+5) periods and for 2024 5 periods (corresponding to the month of May), this in order to have the data in a nuance and then perform time series analysis by matured year.
Do you have any posts about summary tables that you mention or something similar?
Thanks!
Best regards

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.  To your matrix visual, drag Month name to the row labels and year to the column labels.  Write these measures

Measure = sum(Data[Amount])

YTD = calculate([Measure],datesytd(calendar[date],"31/12"))

Hope this helps.

[Greg_Deckler]

@Syndicate_Admin Add an Index column in Power Query Editor and Year column YEAR([fecha]) as DAX in the Desktop. Then a running total measure: Better Running Total - Microsoft Fabric Community