Dax Formula Help - Filters

[Harper146]

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

[v-shex-msft]

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]

Hi Xiaoxin,

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

Fiscal Year	Fiscal Period	Product	New Customers	New Turnover	Retained customers	Retained Turnover
2017	1	Product A	500	10,000	3,000	30,000
2017	1	Product B	200	5,000	2,000	25,000
2017	2	Product 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

[v-shex-msft · Accepted solution]

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]

Thanks Xiaoxin, works perfectly