CODEWITHSUNDEEP
datecalendarpowerbidax
NiteOwl
NiteOwl

Reputation: 13

DAX Expression for calculating end of every year

I am looking for DAX expression to show me monthly figure for last financial years. Here is an example of the information;

The figure is accumulated since the starting of 31 Jan 2017 or earlier which I have no information. The data I have does not have a monthly view but Year to Date (YTD).

How could I structure the DAX so that it would be able to find out what is the figure for the month.

For example, Jan 2017 $2,000 , Feb 2017 $5000 (included figure from Jan 2017), March 2017 $8000 (included Jan 2017 and Feb 2017).

How can it be done to have the monthly view ?

Date............. Balance Sheet Amount.......View

31-Dec 2017................24,000.......................YTD *1

31-Jan 2018................24,010.......................YTD *1

28-Feb 2018................24,310.......................YTD *1

31-Dec 2018................30,000.......................YTD *2

31-Dec 2019................31,000.......................YTD *3

31-Dec 2020................40,000.......................YTD *4

30-June 2021................5,000.......................YTD *5

Upvotes: 1

Views: 515

Answers (1)

Kin Siang
Kin Siang

Reputation: 2699

Let's say your current table does not contain previous balance, then it will be easier to obtain the current month movement with the following formula:

Current Month = Sheet1[YTD Bal] - 
                    CALCULATE(SUM(Sheet1[YTD Bal]),
                    FILTER(Sheet1,Sheet1[Index] = EARLIER(Sheet1[Index]) - 1))

Before you start the dax calculation, you will need to add index column first and here is the output:

enter image description here

Upvotes: 1

Related Questions