topic Re: STATUS HISTORY RANGE TIME in DAX Commands and Tips

STATUS HISTORY RANGE TIME

Joaomatos2002 — Tue, 05 Nov 2024 17:48:55 GMT

Good afternoon,

At the moment, I am working on a report and encountered a challenge. I have a table that shows the status change history for various cards over time. A card can be blocked multiple times and can also be reactivated multiple times after being blocked. My goal is to navigate through this historical table. For example, a card has been active since January, but in March it became blocked. When I select the month of February, this card will be shown as active, but when I select the month of October, it will appear as blocked. This applies to multiple cards because my aim is to calculate both the total and cumulative totals of cards in an active status or any other selected status (depending on the analysis) from the beginning of the data up to the selected month. This is possible thanks to the saved history. The purpose of this is to understand the status of cards over time when selecting different dates.

I will send a sample table of the data.

client	CARD_NUMber	date	valuenew	value old	date/time
1	CARNUMBER	09/10/2024	ACTIVO	BLOQ_DM	09/10/2024 10:35
1	CARNUMBER	07/10/2024	BLOQ_DM	ACTIVO	07/10/2024 10:40
1	CARNUMBER	25/09/2024	BLOQ_DM	ACTIVO	25/09/2024 18:11
1	CARNUMBER	20/09/2024	ACTIVO	BLOQ_DM	20/09/2024 10:05
1	CARNUMBER	19/09/2024	BLOQ_DM	ACTIVO	19/09/2024 17:13
1	CARNUMBER	05/09/2024	BLOQ_DM	BLOQ_LC	05/09/2024 17:34
1	CARNUMBER	04/09/2024	BLOQ_LC	ACTIVO	04/09/2024 10:01
1	CARNUMBER	14/08/2024	BLOQ_DM	ACTIVO	14/08/2024 17:42
1	CARNUMBER	19/07/2024	ACTIVO	BLOQ_DM	19/07/2024 10:05
2	CARNUMBER3	18/07/2024	BLOQ_DM	ACTIVO	18/07/2024 16:31
2	CARNUMBER3	10/07/2024	ACTIVO	BLOQ_DM	10/07/2024 10:05
2	CARNUMBER3	08/07/2024	BLOQ_DM	ACTIVO	08/07/2024 17:27
2	CARNUMBER3	21/06/2024	ACTIVO	BLOQ_DM	21/06/2024 11:05
2	CARNUMBER3	14/06/2024	ACTIVO	BLOQ_DM	14/06/2024 10:05
2	CARNUMBER3	13/06/2024	BLOQ_DM	ACTIVO	13/06/2024 19:04
2	CARNUMBER3	12/06/2024	ACTIVO	BLOQ_DM	12/06/2024 09:35
2	CARNUMBER3	14/06/2023	ACTIVO	ENVIADO	14/06/2023 17:48
2	CARNUMBER3	02/06/2023	ENVIADO	PROCESS	02/06/2023 12:45
2	CARNUMBER3	01/06/2023	PROCESS	PEDIDO	01/06/2023 08:55

How can I achieve this result?

Re: STATUS HISTORY RANGE TIME

FarhanJeelani — Tue, 05 Nov 2024 18:16:35 GMT

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!

Re: STATUS HISTORY RANGE TIME

Joaomatos2002 — Wed, 06 Nov 2024 17:26:59 GMT

Thank you for your time.
step1, step2 : check
Step 3:

What I want is the latest stats for each card up to a selected date. If I set a date range from 01-01-2024 to 05-11-2024, it should display only the most recent stats of each card, regardless of when that last update was (even if it was, for example, in March). When I add records to a table, it currently shows all stats for each card, which it shouldn't. I only need the most recent stats for each card.

Step 4:

The code works, but it's retrieving all statuses for each card and counting them. For example, if a card has had 150 stats updates, when I filter the report for that card, the total count of cards should be 1, not 150 as it is now.
I would like the cumulative total of active stats within the date range, as the tables should update based on this time interval filter.

Re: STATUS HISTORY RANGE TIME

Anonymous — Thu, 07 Nov 2024 09:36:51 GMT

Hi Joaomatos2002 ,

Please update the formula of measure as below and check if it can return the expected result.

Measure = VAR _date = SELECTEDVALUE ( 'Date'[date] ) VAR _tab = SUMMARIZE ( 'Table', 'Table'[CARD_NUMber], "@maxdate", CALCULATE ( MAX ( 'Table'[date] ), FILTER ( 'Table', 'Table'[CARD_NUMber] = EARLIER ( 'Table'[CARD_NUMber] ) ) ) ) VAR _count = CALCULATE ( DISTINCTCOUNT ( 'Table'[CARD_NUMber] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[CARD_NUMber] = MAXX ( _tab, [CARD_NUMber] ) && 'Table'[date] <= _date && 'Table'[date] <= MAXX ( _tab, [@maxdate] ) ) ) RETURN COUNTROWS ( _tab )

If the above one can't help you figure out, please provide more raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: STATUS HISTORY RANGE TIME

Joaomatos2002 — Fri, 08 Nov 2024 15:35:39 GMT

Thanks for your time...
I apologize for the delay.... but I finally managed to do what I wanted, I had to adjust some things in the codes...
Here are the codes I changed and adjusted.

CardStatusAsOfSelectedDate = VAR SelectedDate = MAX('DateTable'[Date]) -- Define a data selecionada RETURN CALCULATE( LASTNONBLANKVALUE( 'Tabela_B'[VALUE_NEW], MAX('Tabela_B'[VALUE_NEW]) ), FILTER( 'Tabela_B', 'Tabela_B'[CARD_NUM] = SELECTEDVALUE('Tabela_B'[CARD_NUM]) && 'Tabela_B'[CREATEDATE] <= SelectedDate ) )

In the same way that I went to get the last status, I also chose to go to the last date:

LastCreatedOnAsOfSelectedDate = VAR SelectedDate = MAX('DateTable'[Date]) RETURN CALCULATE( MAX('Tabela_B'[CREATEDATE]), FILTER( 'Tabela_B', 'Tabela_B'[CARD_NUM] = SELECTEDVALUE('Tabela_B'[CARD_NUM]) && 'Tabela_B'[CREATEDATE] <= SelectedDate ) )

I use both to calculate the total number of cards:

TotalCards = VAR CardStatusTable = ADDCOLUMNS( SUMMARIZE( 'Tabela_B', 'Tabela_B'[CARD_NUM] ), "LastStatus", [CardStatusAsOfSelectedDate], "LastDate", [LastCreatedOnAsOfSelectedDate] ) RETURN CALCULATE( COUNTROWS(CardStatusTable), FILTER( CardStatusTable, [LastStatus] <> "PED_REJECT" && [LastStatus] <> "CANCELADO" && [LastStatus] <> "" ) )

The only thing I couldn't do was the "accumulated", that is, when I present the measure in the matrix table, the data that should be presented should count from the beginning of the data entry to the current line of the matrix, to have a notion of the evolution over time.

If you think I'm doing something wrong, I would appreciate it if you could also give me some tips!!

If anyone can give a hint in this code that I am missing, I would appreciate it...

Re: STATUS HISTORY RANGE TIME

Anonymous — Tue, 12 Nov 2024 06:23:14 GMT

Hi Joaomatos2002 ,

Thanks for your feedback. Please update the formula of measure [TotalCards] as below and check if it can return the expected result...

TotalCards = VAR CardStatusTable = ADDCOLUMNS ( SUMMARIZE ( 'Tabela_B', 'Tabela_B'[CARD_NUM] ), "LastStatus", [CardStatusAsOfSelectedDate], "LastDate", [LastCreatedOnAsOfSelectedDate] ) VAR _lastdate = MAXX ( CardStatusTable, [LastDate] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'Tabela_B'[CARD_NUM] ), FILTER ( ALLSELECTED ( 'Tabela_B' ), 'Tabela_B'[CREATEDATE] <= SELECTEDVALUE ( 'DateTable'[Date] ) && 'Tabela_B'[CREATEDATE] = _lastdate && NOT ( 'Tabela_B'[VALUE_NEW] IN { "PED_REJECT", "CANCELADO", "" } ) ) )

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards