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

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:

  1. 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.
  2. I would like the cumulative total of active stats within the date range, as the tables should update based on this time interval filter.

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

  • 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