Forum Discussion

ps92's avatar
ps92
Frequent Visitor
2 years ago
Solved

Need help with logging date and information

Hello all, I have a date column, a category column and a value column. I want to be able to sum the value column with the previous column only, and if the sum is 2 (the value can be 1 or 0 for the...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ps92 ,

     

    I suggest you to add an [Index] column in your table. Then create a measure.

    Measure = 
VAR _STEP1 =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Last0Date",
            IF (
                'Table'[Below Target] = 0,
                BLANK (),
                CALCULATE (
                    MIN ( 'Table'[Date] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[Section] ),
                        'Table'[Date] > EARLIER ( 'Table'[Date] )
                            && 'Table'[Below Target] = 0
                    )
                )
            )
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "Running Total",
            SUMX (
                FILTER (
                    _STEP1,
                    [Date] <= EARLIER ( [Date] )
                        && [Section] = EARLIER ( [Section] )
                        && [Last0Date] = EARLIER ( [Last0Date] )
                ),
                [Below Target]
            )
    )
RETURN
    MAXX ( FILTER ( _STEP2, [Index] = MAX ( 'Table'[Index] ) ), [Running Total] )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ps92 ,

 

I suggest you to add an [Index] column in your table. Then create a measure.

Measure = 
VAR _STEP1 =
    ADDCOLUMNS (
        ALL ( 'Table' ),
        "Last0Date",
            IF (
                'Table'[Below Target] = 0,
                BLANK (),
                CALCULATE (
                    MIN ( 'Table'[Date] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[Section] ),
                        'Table'[Date] > EARLIER ( 'Table'[Date] )
                            && 'Table'[Below Target] = 0
                    )
                )
            )
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "Running Total",
            SUMX (
                FILTER (
                    _STEP1,
                    [Date] <= EARLIER ( [Date] )
                        && [Section] = EARLIER ( [Section] )
                        && [Last0Date] = EARLIER ( [Last0Date] )
                ),
                [Below Target]
            )
    )
RETURN
    MAXX ( FILTER ( _STEP2, [Index] = MAX ( 'Table'[Index] ) ), [Running Total] )

Result is as below.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

ps92's avatar
ps92
Frequent Visitor
2 years ago

Hello Rico,

This actually worked to solve the problem I was facing. 

Is there a way to be able to display the date of 2 occurences, and only show the date of the next 2 occurences after a certain time frame?

For example, a measure that gives  me the date of first 2 occurences,i.e 7-11-2023, but if there is 2 occurences in the next 2 weeks, the measure wont give me that date, it will only gicve me the next 2 consecutive occurences that take place after 2 weeks.

I'll try to solve it myself as well. But thank you for the above solution. 🙂