[SOLVED] Power BI Card (new) visual - how to count items per week, month, quarter from the column?

Power BI Card (new) visual - how to count items per week, month, quarter from the column?

I am using Power BI desktop, and I need to create a "Card (new)" visual to show the number of distinct IDs per last week, last month, last quarter, last year (or better for selected week, month, quarter, year from the list of available in the whole table).
My "Card (new)" visual has field wells "Data" and "Tooltips".
And my table "conversations" has columns "id" with data like "1aa-2bb3-4ccc2" and "updatedAt" with date in format “MM/DD/YYYY HH:MM:SS AM/PM”. I also created a new Measure which has a number of the week of the year, extracted from "updatedAt" column:

UpdatedAtWeek = WEEKNUM('conversations'[updatedAt] + 1) +
              IF(WEEKDAY(DATE(YEAR('conversations'[updatedAt]), 1, 1)) = 7, -1, 0)

Is it possible to group by that UpdatedAtWeek value, and count unique ids in time range of week, month, quarter (last one or chosen from all available)?

Solution

A Date or Calendar table helps because you can use DAX Time Intelligence functions, and do the groupings that you are after.

The Date table will be the Dimension table to your 'Fact' table. You would use the Date table for your slicers/filters.

You can create a Date table in PowerQuery, DAX as a Calculated Table, or even import it from a data source/warehouse - do a search, you'll find many examples.

An example DAX Calculate Table could look like:

Dim Date =
  ADDCOLUMNS(
    CALENDARAUTO(),
    "Month", FORMAT([Date], "MMM"),
    "MonthNum", MONTH([Date]),
    "Year", YEAR([Date]),
    "Year-Month", FORMAT([Date], "yyyy-MM"),
    "Quarter", FORMAT( QUARTER([Date]), "\Q#"),
    "Year-Quarter", FORMAT([Date], "\Yyy-") & FORMAT( QUARTER([Date]), "\Q#"),
    "WeekNum", WEEKNUM([Date], 16) // this is equivalent to your [UpdatedAtWeek]
  )

Once created:

Click on the Month column in the Dim Date table, then Sort by column in the ribbon, and select MonthNum. This will ensure Jan, Feb etc... will be ordered correctly.
Right-click on Dim Date table and select Mark as date table and in the pop-up select Date as the Date column.

Then for your conversations table - create a Calculated Column just for the Date:

Date = DATE( YEAR([updatedAt]), MONTH([updatedAt]), DAY([updatedAt]) )

Next, add a one-to-many relationship from Dim Date to conversations table on the Date columns.

Lastly, create a new measure for the count of your conversations:

Conversation Count = DISTINCTCOUNTNONBLANK('conversations'[id])

Now you can use [Conversation Count] in your card, and use any of the columns in Dim Date to do your "grouping" via slicers/filters.