topic Re: Trend in the filter in DAX Commands and Tips

Trend in the filter

lukzas — Thu, 01 Aug 2024 14:23:37 GMT

Hi,

I can't solve the problem problem:

I have a table in PowerBI:

Month (YYYY-MM)
Year (YYYY)
Client

Sale

I need to create a filter named Trend (increasing, decreasing, steady). Scenario:

User choose the time interval in the filter Year or Month and choose Increasing in the Trend.

Report should choose the Clients with increasing Sale in the chosen time.

Is it possible?

The difficulty is the lack of a standard date table.

I have access to this table in sqlserver so maybe some transformation in the source would helps..

Thank you for the ideas 🙂

Re: Trend in the filter

Shravan133 — Fri, 02 Aug 2024 01:49:26 GMT

Yes, you can achieve this in Power BI, even without a standard date table. Here’s a step-by-step approach to create a filter named "Trend" that allows users to choose "Increasing," "Decreasing," or "Steady" and shows the clients with the corresponding sales trend:

Create Measures for Trend Calculation

You’ll need to create measures to determine if sales are increasing, decreasing, or steady over the selected time period.

Example Measures:

Total Sales Measure:

TotalSales = SUM('YourTable'[Sale])

Previous Period Sales Measure: For this measure, you will need to use PREVIOUSMONTH or PREVIOUSYEAR depending on whether you are working at a month or year level.

PreviousPeriodSales =

CALCULATE(

[TotalSales],

DATEADD('YourTable'[Month], -1, MONTH) // For monthly data

// DATEADD('YourTable'[Year], -1, YEAR) // For yearly data

)

Sales Trend Measure: Determine the trend based on the comparison between the current period's sales and the previous period's sales.

SalesTrend =

SWITCH(

TRUE(),

[TotalSales] > [PreviousPeriodSales], "Increasing",

[TotalSales] < [PreviousPeriodSales], "Decreasing",

"Steady"

)

Create a Trend Filter

Create a slicer to allow users to select the trend type.

Create a Table for Trend Options: In Power BI Desktop, go to the Modeling tab, select "New Table," and enter the following DAX:

TrendOptions =

DATATABLE(

"Trend", STRING,

{ {"Increasing"}, {"Decreasing"}, {"Steady"} }

)

Add the Trend Filter to the Report: Drag the Trend column from the TrendOptions table into a slicer visual on your report.

Filter Data Based on Selected Trend

To filter the data based on the selected trend, you need to apply a visual filter or create a calculated table based on the trend selection.

Using Visual Level Filters:

Add a Table or Matrix Visual: Drag the Client field and TotalSales measure into a Table or Matrix visual.
Apply a Filter: Use the SalesTrend measure created earlier in the visual level filters pane. Set it to show only items where SalesTrend matches the selected trend.

Using a Calculated Table (Alternative Approach):

You could also create a calculated table that filters clients based on the selected trend.

Create a Calculated Table:

FilteredClients =

VAR SelectedTrend = SELECTEDVALUE('TrendOptions'[Trend])

RETURN

FILTER(

SUMMARIZE(

'YourTable',

'YourTable'[Client],

"TotalSales", [TotalSales],

"SalesTrend", [SalesTrend]

[SalesTrend] = SelectedTrend

)

Use the Filtered Table in Your Report: Use the FilteredClients table in your visuals to show only the clients with the selected sales trend.

Update SQL Source (Optional)

If you prefer to do transformations in SQL Server, you could pre-calculate some of these metrics and trends in your SQL queries or views to make it easier to work with in Power BI.

Re: Trend in the filter

Joe_Barry — Fri, 02 Aug 2024 07:07:04 GMT

Hi lukzas

You can create a date table by following the instructions here. https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

When you create the basic Calendar table create a custom column for (YYYY-MM)

Year/Month = FORMAT('Calendar'[Date], "YYYY-MM")

Create a Many to Many Relationship from the new column above to the Month column in your table. Force the relationship so that Calendar filters your table. click on Cross Filter direction and choose the option I mentioned.

How do you want to create a trend, you need something to compare against a KPI? If you can provide some context and a range you want to measure against, we could use a similar measure below to calculate the last 3 months for example and compare it against the KPI

Last 3 months template measure

Last 3 Months = CALCULATE ( [YourSalesMeasure], DATESINPERIOD ( 'Calendar'[Date], MAX ( 'DIM Date'[Date] ), -3, MONTH ) )

Joe

Re: Trend in the filter

lukzas — Mon, 05 Aug 2024 07:17:32 GMT

thank you, but:

the solution filter only months when it is a increase, but for example :
- 2023-01 increase
- 2023-02 increase
- 2023-03 decrease
- 2023-04 increase
so the overall trend is increasing and if the user choose increasing and time period from 2023-01 to 2023-04 , the report should give those months with the clients (not only 01,02,04)

Re: Trend in the filter

Anonymous — Mon, 05 Aug 2024 02:33:04 GMT

Hi lukzas ,

Thanks to Shravan133 and Joe_Barry for their quick replies. Did their replies solve your problem? If so, please mark helpful replies as solutions. If no, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. Thank you.

Best Regards,

Neeko Tang