CLOSINGBALANCEMONTH dax to show sales of last sales entry rather than the last date.

[han_rj]

For 2014 Febuary , I want sales CLOSINGBALANCEMONTH  dax to show the sales of 02/27/2014 rather than displaying blank.

[Anonymous]

Hi @han_rj ,

Your expression runs great in my sample data, I also noticed in your screenshot that there is data returned for both January and March, and with 2014 being a leap year, please make sure you have data for February 29th.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[han_rj]

Hi @Anonymous , Thank You for Checking and Responding, Yes the closingbalancemonth dax works fine for months which have data until the month end, My question was for cases like Feb 2014 data is not available until the month end and the sale entry is only till 27/02/2014, Is it possible to alter the closingbalancemonth dax to display the latest sales entry [27/02/2014 - $4] rather than displaying it as blank.

[Anonymous]

Hi @han_rj ,

Try changing MAX to ENDOFMONTH and it will return the last day of the month.

ClosingMonthBalance =
VAR max_date = ENDOFMONTH(Orders[Order Date])
RETURN --max_date
CLOSINGBALANCEMONTH(SUM(Orders[Quantity]),Date_Table[Date],Date_Table[Date] == max_date)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[han_rj]

Thank You for responding, I did try the change, It did not work as expected

[Anonymous]

Hi @han_rj ,

That means you have rows of 28, 29, but the data is 0 or blank, try the following expression:

ClosingMonthBalance =
VAR max_date = ENDOFMONTH(Orders[Order Date])
RETURN --max_date
IF(max(orders[Quantity]<>0,CLOSINGBALANCEMONTH(SUM(Orders[Quantity]),Date_Table[Date],Date_Table[Date] == max_date),"")

If 0 did not work, change it to blank().

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[han_rj]

No still the same case

[han_rj]

Thank You for Checking, I would like to reiterate my question closingmonthbalance dax  will bring in the sales for the last date of the month, My question is , is it possible to alter closingmonthbalance to bring in sales of the max date where sales is available for cases where the month end sales is blank/null.

Example Scenario:

For the Year Feb 2014, Has 28 days and Feb 28 the last date does not has sales is it possible to make closingmonthbalance dax to pick up sales on Feb 27 since this is the latest sales recorded.

[Anonymous · Accepted solution]

Hi @han_rj ,

That may require the use of other approaches, check the results below:

Measure = var _t = ADDCOLUMNS('Sheet3',"Last",MAXX(FILTER(ALL('Sheet3'),YEAR([Date])=YEAR(EARLIER([Date]))&&MONTH([Date])=MONTH(EARLIER([Date]))&&[Value]<>BLANK()),[Date]))
RETURN MAXX(FILTER(_t,[Date]=MAXX(_t,[Last])),[Value])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @han_rj ,

I think it's probably not the expression that's the problem anymore; I've been able to return results with either your original expression or my improved one. Is there an exception or relationship to your February date?

Best regards,
Community Support Team_ Scott Chang

[han_rj]

No specific relationship as such,Can You please send me a sample screen short of the data post using the updated DAX

[Anonymous]

Hi @han_rj ,

As you can see, my last day of February is 200, March is blank, and the final result returned is as follows:

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[han_rj]

@BeaBF , Its a kaggle dataset, This Link Has the dataset

https://www.kaggle.com/datasets/vivek468/superstore-dataset-final

[BeaBF]

@han_rj Can you paste here some data? and your measure code? or send the pbix file

[han_rj]

I am not able to paste the data

ClosingMonthBalance =

VAR max_date = MAX(Orders[Order Date])

RETURN --max_date

CLOSINGBALANCEMONTH(SUM(Orders[Quantity]),Date_Table[Date],Date_Table[Date] == max_date)

[BeaBF]

@han_rj Hi! Can you paste some sample data?