Forum Discussion
Translate Excel formulas into Measure using DAX
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.
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!RegardsJorgeAbiadJorgeAbiad 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.
