cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Power Bi Slice

• Good Afternoon;

May you please kindly help me with a Dax formula I can use for the following problem I have: I would like to calculate the sales difference between any two selected months on the filter. I have a month filter and each an every month there are customer accounts that keep track of sales.

I have used the following Dax :

Calculate(sum(sales), Date in {max(month number)}) - Calculate(sum(sales), Date in {min(month number)}) the formula works when a specific account performed transaction for the selected month if there is no transaction it return 0.

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

Please create a new [Year] field in the calendar table, and then try this measure.

``````Change in sales based on two selected months =
VAR _year =
MAX ( 'Master Date Table'[Year] )
VAR _min_month =
MIN ( 'Master Date Table'[Month Number] )
VAR _max_month =
MAX ( 'Master Date Table'[Month Number] )
VAR _sales1 =
CALCULATE (
[tptal],
FILTER (
'Master Date Table',
'Master Date Table'[Month Number] = _max_month
&& 'Master Date Table'[Year] = _year
)
)
VAR _sales2 =
CALCULATE (
[tptal],
FILTER (
'Master Date Table',
'Master Date Table'[Month Number] = _min_month
&& 'Master Date Table'[Year] = _year
)
)
VAR _change = _sales1 - _sales2
RETURN
_change``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

3 REPLIES 3
Super User

@Anonymous

Do you have a seperate dates table or are you having a single table?
Please share the screen shot of the tables with filed names and show you do you select the dates?

Did I answer your question? Mark my post as a solution! and hit thumbs up
Anonymous
Not applicable

Good Fowmy;

Thanks for the response.

I have two tables one is a date table were I added a calculated column to generate month number:

The second table is the transaction table called FAZA Data

The are related in a one to many relationship as shown:

The aim is to have a Dax formula that will calculate the difference in sales between two selected months.

The following dax calculation works if an account perfomed a transaction in both selected month :

But it fails if there was no transaction, it rertuns a zero instead of correctly doing the calculation. Please see the example below:

I selected two month May and July, for this account(A36676) the customer made a transaction in May 5 523.28 but made no transaction in July. I wanted the result to be 0 - 5523.28 = -5523.20 the formula is returning zero.

Community Support

Hi @Anonymous ,

Please create a new [Year] field in the calendar table, and then try this measure.

``````Change in sales based on two selected months =
VAR _year =
MAX ( 'Master Date Table'[Year] )
VAR _min_month =
MIN ( 'Master Date Table'[Month Number] )
VAR _max_month =
MAX ( 'Master Date Table'[Month Number] )
VAR _sales1 =
CALCULATE (
[tptal],
FILTER (
'Master Date Table',
'Master Date Table'[Month Number] = _max_month
&& 'Master Date Table'[Year] = _year
)
)
VAR _sales2 =
CALCULATE (
[tptal],
FILTER (
'Master Date Table',
'Master Date Table'[Month Number] = _min_month
&& 'Master Date Table'[Year] = _year
)
)
VAR _change = _sales1 - _sales2
RETURN
_change``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data