DAX Measures for Line Chart Comparing Rows by Month

This document presents the necessary DAX measures to create a line chart in Power BI to compare the row count across different months for the most recent two years. The approach will take into account the rules for date filtering specified.

Definitions

• Dataset: The input data contains various "as of" dates.
• Last Date of Month: For months prior to the current month, the last date in each month is considered.
• Selected As of Date: For the current month, only the user-selected "as of" date is respected.

DAX Measures

1. Measure to Count Rows

This measure counts the rows in the dataset based on the defined "as of" date criteria.

CountRowsByDate = 
VAR SelectedDate = MAX(DateTable[Date])
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
RETURN
    COUNTROWS(
        FILTER(
            YourDataTable,
            // Check for months before the current month
            MONTH(YourDataTable[AsOfDate]) < CurrentMonth &&
            YEAR(YourDataTable[AsOfDate]) = CurrentYear
            && YourDataTable[AsOfDate] = 
                CALCULATE(
                    MAX(YourDataTable[AsOfDate]),
                    FILTER(
                        YourDataTable,
                        MONTH(YourDataTable[AsOfDate]) = MONTH(YourDataTable[AsOfDate]) &&
                        YEAR(YourDataTable[AsOfDate]) = CurrentYear
                    )
                )
        )
    ) +
    COUNTROWS(
        FILTER(
            YourDataTable,
            // Check if it is the current month
            MONTH(YourDataTable[AsOfDate]) = CurrentMonth &&
            YEAR(YourDataTable[AsOfDate]) = CurrentYear &&
            YourDataTable[AsOfDate] = SelectedDate
        )
    )

2. Measure for Last Date of Each Month (Prior to Current Month)

This measure returns the last date for each month before the current month.

LastDateBeforeCurrentMonth = 
CALCULATE(
    MAX(YourDataTable[AsOfDate]),
    FILTER(
        YourDataTable,
        MONTH(YourDataTable[AsOfDate]) < MONTH(TODAY()) &&
        YEAR(YourDataTable[AsOfDate]) = YEAR(TODAY())
    )
)

3. Measure for Count of Rows up to Selected Date in Current Month

This measure refines the data for the current month based on the selected date.

CountRowsCurrentMonth = 
CALCULATE(
    COUNTROWS(YourDataTable),
    FILTER(
        YourDataTable,
        MONTH(YourDataTable[AsOfDate]) = MONTH(TODAY()) &&
        YEAR(YourDataTable[AsOfDate]) = YEAR(TODAY()) &&
        YourDataTable[AsOfDate] <= MAX(DateTable[Date])
    )
)

Putting It All Together

You can now create a line chart in Power BI using the following steps:

1. Add the Date Dimension: Ensure you have a proper Date table connected to your data.
2. Create a Line Chart: Drag the month/year from the Date table to the X-axis.
3. Use the Primary Measure: For the Y-axis, use the CountRowsByDate measure.
4. Add a Filter: Use the Date slicer to allow selection of dates for the current month.

Usage Example

1. Select Dates: When a user selects an "as of" date from a slicer for the current month, the CountRowsByDate measure will only consider counts based on that selected date.
2. View in Line Chart: The resulting line chart will show the comparison of counts for the past two years, using the last available date for each month before the current, and the selected date for the current month.

Conclusion

By implementing the above measures, you can efficiently visualize monthly data in a line chart comparing counts across different months for the last two years while adhering to the rules you've defined for date selection. For those looking to further enhance their DAX skills, consider exploring resources available on the Enterprise DNA Platform.