Forum Discussion

atad's avatar
atad
Frequent Visitor
7 years ago
Solved

Trailing 12 Average Balance (Measure based on another measure)

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 Ap...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You may download my PBI file from here.

     

    Hope this helps.

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
7 years ago
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
  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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,

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    You may download my PBI file from here.

     

    Hope this helps.

    • atad's avatar
      atad
      Frequent Visitor
      7 years ago

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

       

      I am so impressed by the techniques you applied here!

       

      atad

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        You are welcome.  Thank you for your kind words.

    • SK87's avatar
      SK87
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        Attached here.

    • Mirja_A's avatar
      Mirja_A
      New Member
      2 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        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.