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.

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.

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

        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.

  • 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.

      • Mirja_A's avatar
        Mirja_A
        New Member
        2 years ago

        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