Complex DAX to Calculate Opening Balance?

[Anmolgan]

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/EfoAF6jn9W5MtiM457eiGgwBPoylqI...

Any Ideas how best we can achive the results?

[v-eachen-msft]

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]

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 · Accepted solution]

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] )
    )
)

[Anmolgan]

I will mark this as a solution, since I am not able to validate the values with the SAP system, but the logic mentioned here is correct, thank you soo much, I will reach out to you if any help is needed.