How to design QTD, MTD and YTD for the below listed table structure?

0
votes

[![enter image description here][1]][1]So for one of the clients, I am working on Importing data from GP Server and having a date table imported through excel. So two Tables: Account Summary and DateTable.

Now in Account Summary there are two columns named: Debit Amount and Credit Amount which is coming straight from GP Server. I added a calculated column to the table and named it Net Amount which is a difference between Debit and Credit.

Now since I have Net Amount in place, the client wants to see Year to Date (YTD), Quarter to Date (QTD) of Net Amount and also MTD may be in future. In terms of YTD NetAmount the client wants to see current year and compare it with previous year in terms of a tile. I know this can be achieved by creating a measure using DAX but I have not got a luck yet. The client fiscal year starts from October 1st and ends on September 30th. So for Current year's YTD NetAMount they would want to see data with respect to dates October 1st, 2018 till date, from a filtering perspective this can be done, but I want to explore on how this can be achieved by writing a DAX. I believe by default when we use TOTALYTD function to write measure, PowerBI considers end period as December 31st but I want to customize this to make this run from October 1st to September 30th for a given year.I do have a fiscal year and fiscal quarter columns present in the DateTable. I just need to know how YTD could be designed in terms of a date period I mentioned above.

If anyone knows about this, your help will be greatly appreciated.

1
powerbidax

1 Answers

1
votes

TOTALYTD function has an optional parameter for a year-end, if you need to replace the default December 31st. The parameter is a string "Month/Day", which in your case is "9/30":

[Net Amount YTD] = TOTALYTD(SUM('Account Summary'[Net Amount]), DateTable[Date], "9/30")

On a side note, you should not create calculated column for Net Amount. Instead, create 2 measures: [Sum of Debit Amount], [Sum of Credit Amount], and then Net Amount is simply their difference:

[Sum of Net Amount] = [Sum of Debit Amount] - [Sum of Credit Amount]

Whenever possible, avoid calculated columns and use measures instead.