Forum Discussion

Anmolgan's avatar
Anmolgan
Post Prodigy
6 years ago
Solved

Complex DAX to Calculate Opening Balance?

Hi There, I am trying to create one formula which will give me opening balance of my amount, by passing the given date parameter, now the logic goes like this: 

 

If I pass 02/04/2019 in my parameter, then DAX formula should calculate all the sales of last fiscal year month till the date passed, our Fiscal month starts from April and Ends on March, hence if date is passed as 02/04/2019, then last year last month fiscal month last date 31 March it should calculate upto that so in this case total opening balance from 02/04/2019 - 31/03/2018 this should give me the current opening balance.

 

Now How can I define the date parameter into this which I will take it from user, and how to define the parameter so that if I entered 05/04/2018 it should automaticaly select date 31/03/2017 and calculates amount for that period, Below is the link that I have attached where I am trying to build something close to this (In the PowerBI File Debit credit Value is tha amount, we use posting date to match with the Query1 Date parameter, there is fiscal year also).

 

Links: https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/EfoAF6jn9W5MtiM457eiGgwBPoylqIibsPD2SJzM1MakkQ?e=GJdf50

 

Any Ideas how best we can achive the results?

Need Help
Show and Tell
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anmolgan ,

     

    According to your new logic, you could use the following measure.

    Measure New =
CALCULATE (
    SUM ( 'Monthly Collection Report PBI'[Debit credit value] ),
    FILTER (
        ALLEXCEPT (
            'Monthly Collection Report PBI',
            'Monthly Collection Report PBI'[Customer Group.Customer Group Level 01]
        ),
        'Monthly Collection Report PBI'[Posting date.Posting date Level 01.Key - Copy]
            <= SELECTEDVALUE ( 'Table'[Date] )
    )
)

     

4 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anmolgan ,

     

    I created a new date table ( "Table" ) to get previous fiscal year.

    Then create a measure to calculate the sum.

    Measure =
CALCULATE (
    SUM ( 'Monthly Collection Report PBI'[Debit credit value] ),
    FILTER (
        'Monthly Collection Report PBI',
        'Monthly Collection Report PBI'[Posting date.Posting date Level 01.Key - Copy]
            >= SELECTEDVALUE ( 'Table'[Date2] )
            && 'Monthly Collection Report PBI'[Posting date.Posting date Level 01.Key - Copy]
                <= SELECTEDVALUE ( 'Table'[Date] )
    )
)

    Here is the test file for your reference (pay attention to New Table and New Slicer).

     

    • Anmolgan's avatar
      Anmolgan
      Post Prodigy
      6 years ago

      Hi v-eachen-msft  thanks for the response, can we modify the logic as:

       

      1. Sum of Opening balance is =  passed date >= all the dates from past (Hence sum of all the dates from the past till the date passed in the parameter), 

      for example if I passed date as 01/04/2019 then logic will be Sum of Sales of date 01/04/2019>= Rest of the dates. and that will be the current opening balance for the month of april

      • v-eachen-msft's avatar
        v-eachen-msft
        Community Support
        6 years ago

        Hi Anmolgan ,

         

        According to your new logic, you could use the following measure.

        Measure New =
CALCULATE (
    SUM ( 'Monthly Collection Report PBI'[Debit credit value] ),
    FILTER (
        ALLEXCEPT (
            'Monthly Collection Report PBI',
            'Monthly Collection Report PBI'[Customer Group.Customer Group Level 01]
        ),
        'Monthly Collection Report PBI'[Posting date.Posting date Level 01.Key - Copy]
            <= SELECTEDVALUE ( 'Table'[Date] )
    )
)