Trailing 12 Average Balance (Measure based on another measure)

[atad]

Hi,  

I am still new to DAX.  I have a table that contains general ledger account transactions: account number, date, value.  I also have a date table.  the fiscal year starts on May 1st, ends on April next calendar year (12 fiscal period).  For each gl account, there is an opening balance on May 1st.  At last day of each month, there is an amount representing the total change of the month.  

Now I want to calculate the average balance over the past 12 fiscal period.  Trick part is at first I need to create a measure that calculates the ending balance at each fiscal period.  I was able to create that:

Accumu_Balance =
CALCULATE (
    SUM ( Trsansactions[Value] ),
    FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

I verified that is correct.  However I then encountered issues when creating a measure for the sum of the trailing 12 accumu_balance.  below is the measure for my second formula:

Total_Trailing12Period =
CALCULATE (
    [Accumu_Balance],
    DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -12, MONTH )
)

However, the result is not what I expected.  The result is the same as the Accumu_Balance.  Can someone help with the DAX on the 2nd meausre?  Is it possible to sum the measure based on another measure?

Below is the transaction table and the result of the measures.

I can post the pbx file, but seems couldn't find a place to attach the file.

Thanks in advance!

atad

[TomMartens]

Hey,

please provide an xlsx that contains sample data in one sheet and expected result in another sheet, upload the file to onedrive or dropbox and share the link.

Regards,
Tom

[atad]

First time to use drop box...below are the link to the files, first contains the excel file and expected result.

second is the pbix file.

Thanks,

https://www.dropbox.com/s/vsbk5hqxzvagtbd/BI%20Files.xlsx?dl=0

https://www.dropbox.com/s/sixqiwshxpzriep/trailing12Period_Balance.pbix?dl=0

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Mirja_A]

Hi Ashish,

I have been looking for an answer to my PBI problem and it looks like you were solving here the same kind of dilemma that I'm having at the moment. But I could't get the material you had posted here years back. Any chances to repost it or something like that?

Kind regards,

Mirja A.

[Ashish_Mathur]

Hi,

I do not have that file now.  Share some data (in a format that can be pasted in an SM Excel file), explain the question and show the expected result.

[Mirja_A]

Hi,

I just found the pbix in a post down below. 🙈 Someone else had asked for it. I'll take a look at it first and get back to you with more info if the pbix doesn't solve my problem.

Regards,

Mirja

[SK87]

Hi @Ashish_Mathur 

I am looking something similar, Could you upload the pbix file here as I am not able to open one drive else let me know how you solved this problem.

Thanks in advance.

[Ashish_Mathur]

Attached here.

[SK87]

Hi @Ashish_Mathur 

I am facing bit different problem, kindly guide me on this

1. Suppose if user select Dec'21 in slicer, then the data will filter out as described below:

    In start date column data would be selected less than equal to user selection that is <=Dec'21 and in end date selection would be greater than equal to user selection >-Dec'21. 
which I can get by below measure:

VAR Fmonth= MAX('calendartable'[StartofMonth])

VAR Lmonth= MAX('calendartable'[EO Month])

Return

CALCULATE(

    COUNT('Data'[Categories]),

    FILTER('Data','Data'[End Date] >=Fmonth && 'Data'[Start Date] <= Lmonth))

There is no relationship between calendar table and main table to get coorect solution for above point.

2. Now I want trailing 12 months in stacked bar chart with the counts of above measure for respective categories i.e.

Now once above is working I want the counts to be shown in trailing 12 months i.e. if user select Dec'2021 - bars should be trailing 12 months from Jan'21-Dec'21 but counts should be same as point 1

12 Months Measure =

VAR Fmonth= MAX('calendartable'[StartofMonth])

VAR Lmonth= MAX('calendartable'[EO Month])

VAR PP= DATE(YEAR(Fmonth), MONTH(Fmonth)-12,DAY(Fmonth))

Return

CALCULATE(

    COUNT('Data'[Categories]),

    FILTER('Data','Data'[End Date] >= Fmonth && 'Data'[Start Date] <= Lmonth),ALL('calendartable'[EO Month]),FILTER('calendartable','calendartable'[EO Month]>= PP))

Now when I created the Chart :

X-axis: Calendartable[EOMONTH]

Y-axis: 12 Months Measure

Legend: Categories displayed

Filter: Rank

counts are coming fine but Trailing 12 months not working

Kindly suggest. Thanks in advance.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

With the MS Excel file closed, open PowerBI Desktop and go to Import > Power Query, PowerPivot and Power View.  The DAX formulas and tables will now appear in the PBI file.  Please study the solution.

[atad]

That's amazing Ashish!  works like a charm!  thanks so much!

I am so impressed by the techniques you applied here!

atad

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[v-cherch-msft]

Hi @atad 

You may try below measure and drag Year and Month column into the visual.

Total_Trailing12Period =
CALCULATE (
    SUM ( Trsansactions[Value] ),
    DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -12, MONTH )
)

Regards,