Power Bi Slice

[Anonymous]

• 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.

[Fowmy]

@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?

[Anonymous]

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.

[v-cgao-msft · Accepted solution]

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

Please check the pbix file.

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