Dax measure for below problem

[ARUN_DESHPANDE]

Please provide the Closing balance DAX measure

1. Calendar table
2. Inventory transaction fact table with below columns

Posting date date type

Material code type text

Quantity   Int64.type
Amount   Int64.type

Movement type Int64.type

1. Want Daily, Monthly, quarterly, Yearly closing balance  

[wardy912 · Accepted solution]

Hi @ARUN_DESHPANDE 

Closing Balance :=
CALCULATE(
    SUM(Inventory[Quantity]),
    FILTER(
        ALL(Calendar),
        Calendar[Date] <= MAX(Calendar[Date])
    )
)

This works for Daily, Monthly, Quarterly, and Yearly because the MAX(Calendar[Date]) changes based on the visual’s granularity.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[Kedar_Pande · Accepted solution]

@ARUN_DESHPANDE 

Daily Closing Balance =
CALCULATE(
SUM('Inventory'[Quantity]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)

Monthly Closing Balance =
CLOSINGBALANCEMONTH(SUM('Inventory'[Quantity]), 'Calendar'[Date])

Quarterly Closing Balance =
CLOSINGBALANCEQUARTER(SUM('Inventory'[Quantity]), 'Calendar'[Date])

Yearly Closing Balance =
CLOSINGBALANCEYEAR(SUM('Inventory'[Quantity]), 'Calendar'[Date])

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[Arul · Accepted solution]

@ARUN_DESHPANDE ,

1.

Daily Closing Balance =
CALCULATE (
    SUM ( 'Inventory'[Quantity] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

2.

Monthly Closing Balance =
VAR _LastDay =
    EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
RETURN
CALCULATE (
    [Daily Closing Balance],
    'Calendar'[Date] = _LastDay
)

3.

Quarterly Closing Balance =
VAR _LastDayQuarter =
    ENDOFQUARTER ( 'Calendar'[Date] )
RETURN
CALCULATE (
    [Daily Closing Balance],
    'Calendar'[Date] = _LastDayQuarter
)

4.

Yearly Closing Balance =
VAR _LastDayYear =
    ENDOFYEAR ( 'Calendar'[Date] )
RETURN
CALCULATE (
    [Daily Closing Balance],
    'Calendar'[Date] = _LastDayYear
)

[v-echaithra]

Hi @ARUN_DESHPANDE ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @ARUN_DESHPANDE ,

Thank you @Kedar_Pande , @Arul , @Praful_Potphode , @wardy912  for your inputs.

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!

Thank you 
Chaithra E.

[Praful_Potphode · Accepted solution]

hi @ARUN_DESHPANDE ,

try below:

Closing Balance KeepFilters = 
VAR LastDate = MAX('Calendar'[Date])
RETURN
    CALCULATE(
        SUM('Inventory Transaction'[Quantity]),
        KEEPFILTERS('Inventory Transaction'[Posting date] <= LastDate)
    )

Closing Balance CalcTable = 
VAR LastDate = MAX('Calendar'[Date])
VAR FilteredTransactions = 
    CALCULATETABLE(
        'Inventory Transaction',
        'Inventory Transaction'[Posting date] <= LastDate
    )
RETURN
    SUMX(FilteredTransactions, [Quantity])

If it doesnt work, please provide more information on input/output.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Arul · Accepted solution]

@ARUN_DESHPANDE ,

1.

Daily Closing Balance =
CALCULATE (
    SUM ( 'Inventory'[Quantity] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

2.

Monthly Closing Balance =
VAR _LastDay =
    EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
RETURN
CALCULATE (
    [Daily Closing Balance],
    'Calendar'[Date] = _LastDay
)

3.

Quarterly Closing Balance =
VAR _LastDayQuarter =
    ENDOFQUARTER ( 'Calendar'[Date] )
RETURN
CALCULATE (
    [Daily Closing Balance],
    'Calendar'[Date] = _LastDayQuarter
)

4.

Yearly Closing Balance =
VAR _LastDayYear =
    ENDOFYEAR ( 'Calendar'[Date] )
RETURN
CALCULATE (
    [Daily Closing Balance],
    'Calendar'[Date] = _LastDayYear
)

[Kedar_Pande · Accepted solution]

@ARUN_DESHPANDE 

Daily Closing Balance =
CALCULATE(
SUM('Inventory'[Quantity]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)

Monthly Closing Balance =
CLOSINGBALANCEMONTH(SUM('Inventory'[Quantity]), 'Calendar'[Date])

Quarterly Closing Balance =
CLOSINGBALANCEQUARTER(SUM('Inventory'[Quantity]), 'Calendar'[Date])

Yearly Closing Balance =
CLOSINGBALANCEYEAR(SUM('Inventory'[Quantity]), 'Calendar'[Date])

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[wardy912 · Accepted solution]

Hi @ARUN_DESHPANDE 

Closing Balance :=
CALCULATE(
    SUM(Inventory[Quantity]),
    FILTER(
        ALL(Calendar),
        Calendar[Date] <= MAX(Calendar[Date])
    )
)

This works for Daily, Monthly, Quarterly, and Yearly because the MAX(Calendar[Date]) changes based on the visual’s granularity.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!