CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktop
Laki
Laki

Reputation: 99

Percentage of change with 2 data slicers in Power BI

I have a scenario with two data slicers. The first data slicer filters data for one period, the second one for another period. By editing visual interactions I got this works at the same page. Now I want to compare two resulting values (in this case, number of transactions, and find a percentage of change between two selected periods.

I duplicated data column so I have two date columns for each slicer and I calculated the next measures:

# of Transactions 1 = CALCULATE(COUNT(Report[ProductID]),DATESBETWEEN(Report[Date1],[Start Date 1],[Last Date 1]))
# of Transactions 2 = CALCULATE(COUNT(Report[ProductID]),DATESBETWEEN(Report[Date2],[Start Date 2],[Last Date 2]))
% Transaction Change = ([# of Transactions 1]/[# of Transactions 2]) - 1

The first 2 measures are accurate (# of Transactions 1 & 2), but % of change doesn't work. If you look at the screenshot below, you'll see # od Transactions 1 = 1,990 and # of Transactions 2 = 2,787. I want to compare this 2 values now.

How can I solve this?

enter image description here

Thank you.

Upvotes: 0

Views: 2338

Answers (1)

StelioK
StelioK

Reputation: 1781

First create two measure for your date bounds:

Min Date :=
MIN ( 'Report'[Date] )
Max Date :=
MAX ( 'Report'[Date] )

Then create a date table using the following DAX, this will join to you 'Report' table on the primary date:

Dates :=
VAR MinDate = [Min Date]
VAR MaxDate = [Max Date]
VAR BaseCalendar =
    CALENDAR ( MinDate, MaxDate )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate =
            YEAR ( BaseDate )
        VAR MonthNumber =
            MONTH ( BaseDate )
        VAR YrMonth =
            100 * YEAR ( BaseDate )
                + MONTH ( BaseDate )
        VAR Qtr =
            CONCATENATE ( "Q", CEILING ( MONTH ( BaseDate ) / 3, 1 ) )
        VAR YrMonthQtr =
            100 * YEAR ( BaseDate )
                + MONTH ( BaseDate )
                & CONCATENATE ( "Q", CEILING ( MONTH ( BaseDate ) / 3, 1 ) )
        VAR YrMonthQtrDay =
            100 * YEAR ( BaseDate )
                + MONTH ( BaseDate )
                & CONCATENATE ( "Q", CEILING ( MONTH ( BaseDate ) / 3, 1 ) )
                & DAY ( BaseDate )
        RETURN
            ROW (
                "Day", BaseDate,
                "Year", YearDate,
                "Month Number", MonthNumber,
                "Month", FORMAT ( BaseDate, "mmmm" ),
                "Year Month", FORMAT ( BaseDate, "mmm yy" ),
                "YrMonth", YrMonth,
                "Qtr", Qtr,
                "YrMonthQtr", YrMonthQtr,
                "YrMonthQtrday", YrMonthQtrDay
            )
    )

Now create another date table from which to compare, and join to your primary date table in 'Report' and ensure the relationship is inactive:

Compare Dates :=
ALLNOBLANKROW ( 'Dates' )

Now create the [# of transaction] measure; one for 'Dates' and another for 'Compare Dates' like so:

[# of Transaction 1] :=
CALCULATE (
    COUNT ( Report[ProductID] )
)

[# of Transaction 2] :=
CALCULATE (
    [# of transaction 1],
    ALL ( 'Dates' ),
    USERELATIONSHIP ( 'Compare Dates'[Date], 'Report'[Date] )
)

Now Create the % Delta measure:

Transaction Change := CALCULATE(DIVIDE([# of Transactions 1],[# of Transactions 2]) - 1)

This should work like a charm and will work for any dates selected in your slicers, you will still need to associate your date slicers with your new date tables.

I hope this helps!!

Upvotes: 1

Related Questions