Forum Discussion

Icon for Helper III rank

Helper III

6 years ago

Translate Excel formulas into Measure using DAX

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. Above, I have 2 pictures. The data here will be placed...

Icon for Community Support rank

Community Support

6 years ago

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
Helper III
6 years ago
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
  Community Support
  6 years ago
  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!

Related content