Translate Excel formulas into Measure using DAX

[JorgeAbiad]

Hello PBI Experts,

I am making a report based on Excel data.

However, I am stuck on how to translate the excel formula using DAX.

Pic1

Pic2

Above, I have 2 pictures. The data here will be placed in a Matrix visual. I was able to do the first picture, meaning data have been transfered to a Matrix. Now, I'm having difficulty constructing the measure using Dax for the second picture.

As you can see, the data of 1st column in the second pic is equal to the data of 1st column in the first pic. The next column in second pic is obtained by adding the cell values of 1st column in second pic and the cell values of 2nd column in the first pic. The process is repeated for the succeeding columns.

I created a tabular representation of the process shown below.

Pic3

My question is how to do the successive addition using Dax. The columns in the pic1 and 2 are the same columns in actual Excel file. Each column represent the summarization of values of values per month column.

Please help.

Regards,

JorgeAbiad

[v-kelly-msft]

Hi @JorgeAbiad ，

I make a simple example as below：

Here is the table:

First create a calculated column to get the month:

Month = MONTH('Table'[Date])

Then create a measure as below:

accumulation = SUMX(FILTER(ALL('Table'),'Table'[Month]<=MAX('Table'[Month])),'Table'[Sale])

Finally you will see:

 For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[JorgeAbiad]

Hi @v-kelly-msft 

Thank you very much for prompt reply. I will test on my end.

By the way, I forgot to mention in my message that I have a separate table for the Dates. This is a calendar table i created inside Power BI (with one-to-many relationship with Budget sheet). And the month column from this table is the one I used for the columns of the matrix.

I have a Budget sheet with column dates from Jan, 2020 to Dec 2020. Each column has more than 2000 rows. My question is how do I correctly get the subtotal for each month using a measure?

I am using the below formula:

BUD 2020_tbl = CALCULATE(SUM(BUD20_Sheet[Bud. Rev. incl. Adj.2020 BFI]), FILTER(BUD20_Sheet, RELATED(CalendarTable[CalendarYear/Month])))

Is it correct? The subtotal for each column will then be placed in a matrix with 12 columns from Jan - Dec.

Thank you very much

Regards,

JorgeAbiad

[v-kelly-msft]

Hi @JorgeAbiad ,

Your measure is right to calculate the subtotal for each month,as you see below:

And if you wanna use the calendar date as the column in matrix,you may try below measure:

accumulation = 
IF(MAX('Table'[Date]) in FILTERS('Calendar'[Date]),SUMX(FILTER(ALL('Table'),'Table'[Month]<=MAX('Table'[Month])),'Table'[Sale]),BLANK())

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AllisonKennedy]

Hi @JorgeAbiad 

Do you have a separate table for the dates (Jan, Feb, Mar, ...)

What you are wanting is a YTD total, so try using either TOTALYTD or use as a MEASURE:

Table2 CumulativeTotal=CALCULATE(SUM(Table[Value]),DATESYTD(DateTable[Date]))

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[JorgeAbiad]

Hello @AllisonKennedy ,

Thank you for your prompt reply. Yes, I have a separate table for the Dates. This table has 1-to-many relationship with the Excel sheets. I will test this solution you provided.

I have another question, what is the optimal way to get the subtotal of each column. These subtotals will also be placed in a matrix.

For the Budget sheet, I'm using below measure:

"BUD 2020_tbl = CALCULATE(SUM(BUD20_Sheet[Bud. Rev. incl. Adj.2020 BFI]), FILTER(BUD20_Sheet, RELATED(CalendarTable[CalendarYear/Month])))"

Is this correct or is there a better way to optimize the measure?

Thank you very much!

Regards

JorgeAbiad

[AllisonKennedy]

@JorgeAbiad  If you already have the Date table, you don't need such a complex measure, you should just be able to use the Month column from the date table in a matrix along with budget or SUM(Budget) if you want to be explicit and the relationships will do their job without being asked. CALCULATE and FILTER are needed when you want to do something other than the relationships and matrix context, but if I understand you correctly, the date table and budget are already related so should do the trick.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv