Forum Discussion

Harper146's avatar
Harper146
Regular Visitor
8 years ago
Solved

Dax Formula Help - Filters

Hi, I'm new to Power BI and the DAX language. I'm struggling to filter my calculated measure by a dimension. The below Dax formula calculates the previous months new customers in a custom financial...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Harper146,

     

    You can try to use below measure to calculate difference:

    Measure =
VAR fiscYear =
    SELECTEDVALUE ( Table1[Fiscal Year ] )
VAR fiscPeriod =
    SELECTEDVALUE ( Table1[Fiscal Period] )
VAR currProduct =
    SELECTEDVALUE ( Table1[Product] )
RETURN
    CALCULATE (
        SUM ( Table1[New Customers] ),
        VALUES ( Table1[Fiscal Year] ),
        VALUES ( Table1[Fiscal Period] ),
        VALUES ( Table1[Product] )
    )
        - CALCULATE (
            SUM ( Table1[New Customers] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Product] IN VALUES ( Table1[Product] )
                    && Table1[Fiscal Year]
                        = IF ( fiscPeriod = 1, fiscYear - 1, fiscYear )
                    && Table1[Fiscal Period]
                        = IF ( fiscPeriod = 1, 12, fiscPeriod - 1 )
            )
        )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Harper146,

 

SELECTEDVALUE and HASONEVALUE function only works for single row content, it will return blank if current contents contains or summarize multiple records.  So you're formula not works for multiple selection.

 

BTW, you can use allselected function to get multiple selection values, but it obviously can't use on your original condition. Please provide sample sample data and expected result to help us clarify your requirement.

 

Regards,

Xiaoxin Sheng

  • Harper146's avatar
    Harper146
    Regular Visitor
    8 years ago

    Hi Xiaoxin,

     

    Ah that makes sense, thanks. please see some sample data below:

     

    Fiscal Year Fiscal PeriodProductNew CustomersNew TurnoverRetained customersRetained Turnover
    20171Product A                         500                10,000                              3,000                         30,000
    20171Product B                         200                  5,000                              2,000                         25,000
    20172Product A                         400                  9,000                              3,100                         32,000

     

    I'm trying to add a colum with the previous periods transactions to the table which will alow me to calculate the Month on Month change.

     

    Thanks

    Sam

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Harper146,

       

      You can try to use below measure to calculate difference:

      Measure =
VAR fiscYear =
    SELECTEDVALUE ( Table1[Fiscal Year ] )
VAR fiscPeriod =
    SELECTEDVALUE ( Table1[Fiscal Period] )
VAR currProduct =
    SELECTEDVALUE ( Table1[Product] )
RETURN
    CALCULATE (
        SUM ( Table1[New Customers] ),
        VALUES ( Table1[Fiscal Year] ),
        VALUES ( Table1[Fiscal Period] ),
        VALUES ( Table1[Product] )
    )
        - CALCULATE (
            SUM ( Table1[New Customers] ),
            FILTER (
                ALL ( Table1 ),
                Table1[Product] IN VALUES ( Table1[Product] )
                    && Table1[Fiscal Year]
                        = IF ( fiscPeriod = 1, fiscYear - 1, fiscYear )
                    && Table1[Fiscal Period]
                        = IF ( fiscPeriod = 1, 12, fiscPeriod - 1 )
            )
        )

       

      Regards,

      Xiaoxin Sheng

      • Harper146's avatar
        Harper146
        Regular Visitor
        8 years ago

        Thanks Xiaoxin, works perfectly