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 calendar:

 

 PrevMonNActives =
Var CurrentMonth = SELECTEDVALUE(Query[Fiscal Period])
Var CurrentYear = SELECTEDVALUE(Query[Fiscal Year])
Var Maxmonth = calculate( Max (Query[Fiscal Period]), all (Query))

Return
if (HASONEVALUE(Query[Fiscal Period]),
sumx (
FILTER(all (Query),
if(CurrentMonth = 1,
Query[Fiscal Period] = Maxmonth && 'Query'[Fiscal Year] = CurrentYear - 1,
Query[Fiscal Period] = CurrentMonth -1 && Query[Fiscal Year] = CurrentYear)),
Query[NewActives]),
Blank())

 

The formula works when only one product data is entered but when I entered the rest of the products the filter slicer does not apply.

 

Alternativly I created a date column "Date = DATE(Query[Fiscal Year],Query[Fiscal Period],1)"  which makes it much simpler to calculate the previous month and solves the filter issue but the date hierachy only shows month name and it needs to be month number. = Month(query[date]) failed to work as well.

 

A solution to either problem would be much appreciated!

 

Thanks

  • 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

4 Replies

  • 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