Forum Discussion

Joaomatos2002's avatar
Joaomatos2002
Frequent Visitor
1 year ago
Solved

STATUS HISTORY RANGE TIME

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 t...
"DAX "
"DAX " "DAX help" 'Need help'
  • Anonymous's avatar
    Anonymous
    1 year ago

    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", "" } )
        )
    )

    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

Joaomatos2002's avatar
Joaomatos2002
Frequent Visitor
1 year ago

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...

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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", "" } )
        )
    )

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