Power BI and DAX Overview

Setup

To begin with, install Power BI desktop from the official website (https://powerbi.microsoft.com/desktop/) and follow the instructions to setup.

Once it is installed, load your data into Power BI desktop (you can load data from various sources like Excel, Web, SQL Server etc.). After your data is loaded, you could view it in the form of a table in Power BI desktop.

Understanding DAX

DAX (Data Analysis Expressions) is a library of functions and operators which can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel. DAX includes functions that:

• Return a single value (for example, a sum or average)
• Work with related data (for example, combining data from related tables)
• Perform more advanced calculations.

We'll start with a simple DAX function - COUNT - which counts the number of rows in a column.

Get the count of rows where value is positive:

Positive count = 
    CALCULATE (
        COUNT ( 'TableName'[ColumnName] ),
        'TableName'[ColumnName] > 0
    )

Get the count of rows where value is negative:

Negative count = 
    CALCULATE (
        COUNT ( 'TableName'[ColumnName] ),
        'TableName'[ColumnName] < 0
    )

Replace 'TableName' with the name of your table, and "ColumnName" with the name of your column.

Consecutive Days Calculation

Counting the number of consecutive days where value is positive or negative involves taking into consideration the previous day's value and sign. Here, we implement this logic using DAX.

ConsecutiveCountMeasure =
   VAR CurrentValue = SELECTEDVALUE( 'YourTable'[Value] )
   VAR CurrentDate = SELECTEDVALUE( 'YourTable'[Date] )
   VAR PreviousDay =
    CALCULATE(
        MAX( 'YourTable'[Date] ), 
        ALL( 'YourTable'[Date] ),
        'YourTable'[Date] < CurrentDate
    )
   VAR PreviousValue =
    CALCULATE(
        MAX( 'YourTable'[Value] ),
        ALL( 'YourTable'[Value] ),
        'YourTable'[Date] = PreviousDay
    )

RETURN
    IF(
        CurrentValue > 0 && PreviousValue > 0,
        1,
        IF(
            CurrentValue < 0 && PreviousValue < 0,
            -1,
            0
        )
    )

In the above example:

1. CurrentValue gets the value of current day.
2. CurrentDate gets the date of current day.
3. PreviousDay calculates the date of the day prior to the current one.
4. PreviousValue gets the value of the previous day.

It then uses IF function to return 1 if both CurrentValue and PreviousValue are positive (indicative of consecutive positive days), -1 if both are negative (indicative of consecutive negative days), and 0 otherwise.

Replace 'YourTable', 'Value', and 'Date' with respective table name, value column, and date column in your data.

Remember, DAX works in the context of the data it's working with. Therefore, it's crucial to understand the context in order to ensure your expressions return the expected results.

Analyzing Sequential Data using DAX

Prerequisites

1. A Power BI report with a table loaded in the data model
2. A date column with daily sequential dates
3. A measurement column (we refer to it as 'Value' column here)

Please make sure these prerequisites are in place before proceeding.

Implementation

Step 1: Creating Running Total Measure

First, create a Running Total measure which has the sum of 'Value' till the current day.

Running Total = 
CALCULATE(
    SUM('Table'[Value]), 
    FILTER(
        ALL('Table'), 
        'Table'[Date] <= MAX('Table'[Date])
    )
)

Step 2: Creating Day Sequence Measure

Now, let's create a measure that returns a unique identifier for each sequence of positive or negative values. We'll call this measure 'Day Sequence'.

Day Sequence = 
VAR CurrentSign = SIGN([Running Total])
VAR PreviousSign = CALCULATE(
    SIGN([Running Total]), 
    FILTER(
        ALL('Table'), 
        'Table'[Date] = MAX('Table'[Date]) - 1
    )
)
VAR Result = IF(
    CurrentSign = PreviousSign, 
    MAX('Table'[Day Sequence]), 
    MAX('Table'[Day Sequence]) + 1
)
RETURN Result

This DAX code explains are as follows:

• CurrentSign: It's the sign of the running total of the current date row.
• PreviousSign: It's the sign of the running total of the previous date row.
• Result: This checks if the CurrentSign and PreviousSign are the same. If they are, it outputs the max of Day Sequence (which would be the sequence number of the previous day). If they are not the same, it outputs the max of Day Sequence plus 1 — indicating a new sequence has started.

Step 3: Creating Consecutive Day Count Measure

The final step is to create a measure that counts the number of consecutive positive or negative days.

Consecutive Day Count = 
VAR CurrentSequence = [Day Sequence]
VAR CurrentMaxDate = MAX('Table'[Date])
VAR PreviousMaxDate = CALCULATE(
    MAX('Table'[Date]), 
    FILTER(
        ALL('Table'), 
        'Table'[Day Sequence] = CurrentSequence - 1
    )
)
VAR Result = IF(
    CurrentMaxDate = PreviousMaxDate + 1, 
    CALCULATE(
        COUNT('Table'[Date]), 
        FILTER(
            ALL('Table'), 
            'Table'[Day Sequence] = CurrentSequence
        )
    ), 
    1
)
RETURN
Result

This DAX code works as follows:

• CurrentSequence: It's the current date row's Day Sequence value.
• CurrentMaxDate: It's the current date.
• PreviousMaxDate: It's the maximum date where the Day Sequence equals to the current Day Sequence minus 1.
• Result: If there's no day gap between the CurrentMaxDate and the PreviousMaxDate, it counts all the dates with the CurrentSequence. If there's a day gap, it returns 1 — indicating a new sequence has started.

Now, you have implemented the solution for counting the number of consecutive days a column value is positive or negative. Add the 'Running Total', 'Day Sequence' and 'Consecutive Day Count' measures into your Power BI report table or any visuals, you will see the sequential data analysis results.

Implementing the Positive Consecutive Days Count Measure in Power BI using DAX

Section 1: Importing & Preprocessing Data

Let's assume you have your dataset imported in Power BI already. The data contains a column "Value" that we want to check for positive or negative values.

Section 2: Calculate the Sign (Positive or Negative) for each Day

First, we need to check if the value for each day is positive or negative. This can be done with IF statement in a new calculated column:

Sign = IF('Table'[Value] > 0, 1, IF('Table'[Value] < 0, -1, 0))

This expression will add new column "Sign" into Table with 1 if Value is positive, -1 if Value is negative, and 0 if Value is zero.

Section 3: Calculate the GroupId for Consecutive Positive or Negative Days

Next, add a new calculated column GroupId. This column will be unique for each group of consecutive days with the same sign:

GroupId = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Sign] <> EARLIER ( 'Table'[Sign] )
            && 'Table'[Date] < EARLIER ( 'Table'[Date] )
    )
)

This DAX formula calculates the total number of rows from the current row back to first row where the sign changes and assigns a unique GroupId to every single group.

Section 4: Counting the Number of Consecutive Days

The final measure that calculates the number of consecutive positive days can be created with the following DAX statement:

ConsecutivePositiveDays = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[GroupId] = MAX ( 'Table'[GroupId]' )
            && 'Table'[Sign] = 1
    )
)

Here, for each day we count the number of rows having the same GroupId and Sign equal to 1(Positive). The ALLSELECTED function ensures that the calculated measure will respect the filter context which comes from any slicers or visual level filters applied onto your report.

Finally, you can now add this measure to your Power BI report to visualize the count of consecutive positive days based on the selected filter of your choice.

Always remember, the main idea behind this solution is to assign unique GroupId to set of continuous positive or negative numbers and count the number of days based on it.

Please, be aware the dataset should have "Date" field and dates should be continuous, if you have missing dates, you might need to consider creating a date table and joining it with your dataset.

Implementation: Negative Consecutive Days Count Measure

Given the setup in which you're working with Power BI and Data Analysis Expression (DAX) language, you have sequentially structured data and have already implemented the Positive Consecutive Days Count Measure.

In this section, I will implement the Negative Consecutive Days Count measure. This measure calculates the number of consecutive days that a given column value is negative.

Step 1: Add a Negativity Indicator Column

Firstly, the data table needs to indicate whether the daily value is negative. Add a calculated column to your data table with the following formula:

Negativity Indicator = IF([Your Value Column] < 0, 1, 0)

This formula will yield 1 if the daily value is less than 0 (negative) and 0 otherwise.

Step 2: Create a New Measure for Negative Groups

Next, we need to identify groups of consecutive negative days. You can accomplish this using the DAX CALCULATE and FILTER functions. Create a new measure with the following formula:

Negative Group = 
CALCULATE(
  MAX(Table[Negativity Indicator]), 
  FILTER(
    ALL(Table), 
    Table[Date] <= EARLIER(Table[Date]) && Table[Date] >= MINX(FILTER(Table, Table[Negativity Indicator] = 0 && Table[Date] <= EARLIER(Table[Date])), Table[Date])
  )
)

This complex DAX formula operates by:

• Checking all of the current row's previous rows (Table[Date] <= EARLIER(Table[Date])).
• Of these rows, finding the closest date where the value was not negative (Table[Negativity Indicator] = 0).
• Creating a filtered table where the date is in the range between the current date and the closest previous non-negative date.
• Using the CALCULATE function to compute Max of Negativity Indicator for only these records.

Step 3: Accumulate the Negative Days

Next, you need to accumulate the negative days using a calculated column to perform a running total of "Negativity Indicator" column based on the "Negative Group".

Negative Consecutive Days Count = 
CALCULATE( 
    SUM(Table[Negativity Indicator]), 
    FILTER(
        ALL(Table), 
        Table[Negative Group] = EARLIER(Table[Negative Group]) && Table[Date] <= EARLIER(Table[Date])
    )
)

This DAX formula accumulates the negative days count for each group of consecutive negative days independently. It calculates the running total of "Negativity Indicator" only for the rows belonging to the same "Negative Group" and having a less or equal date.

Now, you have a column that keeps count of the consecutive negative days in your dataset. Display this column in a table or visual in Power BI to utilize this measure.

Optimizing Measures and Finalizing the Project

As you already made the first four parts of your project, and are looking at optimization and finalizing your project, let's go through that.

Optimize Measures using variables in DAX

You can significantly increase the performance of your Power BI model by using variables in DAX.

Here’s how you can optimize Positive and Negative consecutive days count measures:

PositiveConsecutiveDaysCountOptimized = VAR _MaxDateInContext = MAX(Table[Date]) RETURN IF ( CALCULATE ( MIN(Table[Date]), Table[Value] < 0 ) < _MaxDateInContext, BLANK (), COUNTROWS ( FILTER ( ALL(Table), Table[Value] > 0 && Table[Date] <= _MaxDateInContext ) ))

NegativeConsecutiveDaysCountOptimized = VAR _MaxDateInContext = MAX(Table[Date]) RETURN IF ( CALCULATE ( MIN(Table[Date]), Table[Value] > 0 ) < _MaxDateInContext, BLANK (), COUNTROWS ( FILTER ( ALL(Table), Table[Value] < 0 && Table[Date] <= _MaxDateInContext ) ))

The optimization here is that "_MaxDateInContext" is computed only once and used multiple times. If you hadn't used a variable, Power BI would calculate it every single time it was referenced in the code.

finalizing report

These consecutive count measures can now be used in your report. In Power BI Desktop, you can visualize these data in different kinds of charts such as bar chart, line chart, etc. depending on your requirements.

For instance, you might want to add the following visual elements to your report:

1. A line chart to show the Positive and Negative Consecutive Days Count measure per day.

2. Tooltips showing the Positive and Negative Consecutive Days Count measure for the given day.

3. A Slicer would also help to select a specific date range.

All visualization elements in Power BI are fully interactive and synchronize with each other, meaning selecting certain data in one visualization element will instantly affect all others.

Publishing

After all these steps, you might want to publish this report to PowerBI Service, which allows your report to be shared within your organization. To do this:

1. Go to the 'File' menu and select 'Publish'.
2. Pick 'Publish to Power BI'.
3. If not already signed in, provide your credentials to proceed.

Please note that PowerBI Service does require an active PowerBI Pro subscription.

Once you've published your report, you can create a Dashboard and pin this report to the Dashboard for quick access.

You've now completed your project. You handled sequential data using DAX and Power BI, implemented and optimized the PositiveConsecutiveDaysCount and NegativeConsecutiveDaysCount measures, finalized your report and published it. Good work!