Financial table with NAV, DTD, MTD, QTD and YTD

MakZH — Wed, 19 Jul 2023 17:32:32 GMT

Dear Community

Did someone implemented a similar financial table already? The requirements are to show the NAV, DTD, MTD, QTD and YTD from different groups.

Selected Date: 2023-06-22
+-----------------+-----------------+-----------------+-----------------+-----------------+

| NAV | DTD | MTD | QTD | YTD |
+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+

| Group Total | 1,000,000 | 15 | 140 | 140 | 500 |

+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+

| Group 1 | 300,000 | 5 | 120 | 120 | 250 |

+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+

| Group 2 | 200,000 | 3 | 9 | 9 | 50 |

+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+

| Group ? | 500,000 | 7 | 11 | 11 | 200 |

+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+

I assume to calculate the MTD, QTD and YTD with the DAX functions "TotalMTD", "TotalQTD" and "TotalYTD".

Thank you very much for your answers.

Re: Financial table with NAV, DTD, MTD, QTD and YTD

technolog — Mon, 04 Sep 2023 17:49:15 GMT

You want to create a financial table in Power BI that shows NAV, DTD, MTD, QTD, and YTD for different groups. You've also provided a sample table structure and mentioned that you're considering using the DAX functions "TotalMTD", "TotalQTD", and "TotalYTD" for the calculations.

Firstly, for the NAV (Net Asset Value), it seems like you have the values directly in your data, so you won't need a DAX formula for that. You'll just display the NAV column from your data source.

For DTD (Day-to-Day), it's not clear from your example how you're calculating it. Typically, DTD would be the difference between the NAV of the selected date and the NAV of the previous day. A DAX formula for DTD might look something like this:

DTD = SUM('Table'[NAV]) - CALCULATE(SUM('Table'[NAV]), DATEADD('Table'[Date], -1, DAY))
This formula calculates the difference between the NAV of the current row's date and the NAV of the previous day.

For MTD (Month-to-Date), you're right in thinking of using the "TotalMTD" function. The formula would look something like:

MTD = TOTALMTD(SUM('Table'[NAV]), 'Table'[Date])
This will give you the cumulative NAV for the month up to the selected date.

For QTD (Quarter-to-Date), similarly, you'd use the "TotalQTD" function:

QTD = TOTALQTD(SUM('Table'[NAV]), 'Table'[Date])
This gives you the cumulative NAV for the quarter up to the selected date.

Lastly, for YTD (Year-to-Date), you'd use the "TotalYTD" function:

YTD = TOTALYTD(SUM('Table'[NAV]), 'Table'[Date])
This will give you the cumulative NAV for the year up to the selected date.

Once you've created these measures, you can then use them in a matrix visual in Power BI. Drag the 'Group' field to the Rows area and the measures (NAV, DTD, MTD, QTD, YTD) to the Values area. This should give you a table similar to the one you've shown.

Remember, these formulas are based on the assumption that you have a 'Date' column in your table and that the NAV values are daily. If your data structure is different, you might need to adjust the formulas accordingly. Hope this helps!

topic Financial table with NAV, DTD, MTD, QTD and YTD in DAX Commands and Tips

Financial table with NAV, DTD, MTD, QTD and YTD

Re: Financial table with NAV, DTD, MTD, QTD and YTD