Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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

Hi @Anonymous ,

 

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

vcgaomsft_0-1661322203025.png

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

vcgaomsft_1-1661322315975.png

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

View solution in original post

3 REPLIES 3
Fowmy
Super User
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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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:

Ano_0-1660840016710.png

The second table is the transaction table called FAZA Data

Ano_1-1660840081167.png

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

Ano_2-1660840124862.png

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 :

Ano_3-1660840251220.png

 

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.

Ano_4-1660840659618.png

 

Hi @Anonymous ,

 

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

vcgaomsft_0-1661322203025.png

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

vcgaomsft_1-1661322315975.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.