To achieve a dynamic report that tracks the status of each card at any given point in time in Power BI, you can follow these steps. The key is to create a calculated column or measure that identifies the status of each card as of the selected date.
Here’s a step-by-step guide to setting it up:
Step 1: Create a Calendar Table
1. Create a Date table if you don’t have one. It should cover the entire range of dates in your dataset.
2. Make sure it has a column for month and year to allow you to filter by month.
Here’s a DAX formula to create the Date table:
DAX
DateTable =
ADDCOLUMNS(
CALENDAR(MIN(YourTable[date]), MAX(YourTable[date])),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"MonthName", FORMAT([Date], "MMM"),
"Year-Month", FORMAT([Date], "YYYY-MM")
)
Replace `YourTable[date]` with the actual date column in your history table.
Step 2: Link the Calendar Table to Your Data Table
1. Go to the Model View in Power BI.
2. Create a relationship between the `DateTable`’s `[Date]` column and your history table’s `[date]` column.
3. Set the relationship as Many-to-One and Single Directional (from `DateTable` to your table).
Step 3: Create a Measure to Identify the Status as of the Selected Date
Use the `LASTNONBLANK` function to get the latest status before or on the selected date. This measure will check the last available status in history up to the selected date for each card.
DAX
CardStatusAsOfSelectedDate =
VAR SelectedDate = MAX(DateTable[Date])
RETURN
CALCULATE(
LASTNONBLANKVALUE(YourTable[valuenew], [valuenew]),
FILTER(
YourTable,
YourTable[CARD_NUMber] = SELECTEDVALUE(YourTable[CARD_NUMber]) &&
YourTable[date] <= SelectedDate
)
)
This measure will dynamically return the latest status up to the selected date for each card.
Step 4: Create Measures for Total and Cumulative Counts
1. Total Count of Active Cards:
This measure calculates the total number of cards in "ACTIVO" status as of the selected date.
DAX
TotalActiveCards =
CALCULATE(
COUNTROWS(YourTable),
FILTER(
YourTable,
[CardStatusAsOfSelectedDate] = "ACTIVO"
)
)
2. Cumulative Total of Active Cards:
This measure calculates the cumulative count of cards that were ever active up to the selected date.
DAX
CumulativeActiveCards =
CALCULATE(
COUNTROWS(YourTable),
FILTER(
ALL(DateTable),
DateTable[Date] <= MAX(DateTable[Date]) &&
[CardStatusAsOfSelectedDate] = "ACTIVO"
)
)
Step 5: Build the Report
1. Add a slicer for `DateTable[Date]` so that you can select any date.
2. Use a Matrix or Table visual to display `CARD_NUMber` and the `[CardStatusAsOfSelectedDate]` measure to see the status of each card at the selected date.
3. Add a Card Visual or KPI to display `TotalActiveCards` and `CumulativeActiveCards` for dynamic counts based on the date selection.
This setup will give you a dynamic view of each card’s status history over time, and you can adjust the date slicer to see the status for any month or cumulative total.
If you have any further customization needs, feel free to ask!
