Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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