Forum Discussion

martipe1's avatar
martipe1
Helper II
1 year ago
Solved

ACR Report Compare Snap Shots

I have an ACR report that uses snap shots from different days.   For every invoice I have the days past due and based on those days I have calculated columns for different aging buckets also the sn...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, martipe1 
    Thank you for your response.

     

    1.Firstly, the reason for creating a new table as a slicer is that if you use the current table directly as a slicer, it will restrict the data displayed to only those selected by the slicer. This approach does not allow for the requirement of selecting a date while displaying both the current date and the selected date.

     

    2.Secondly, based on your updated data structure, you might consider replacing the use of visual calculations with the following measures:

    Variance1 =
FORMAT (
    DIVIDE (
        SUM ( 'Table'[0-30] ),
        CALCULATE (
            SUM ( 'Table'[0-30] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[SnapShot/Days Past Due]
                    = MAX ( 'Table'[SnapShot/Days Past Due] ) - 1
            )
        )
    ),
    "0.00%"
)
    Variance2 = 
FORMAT (
    DIVIDE (
        SUM ( 'Table'[31-60] ),
        CALCULATE (
            SUM ( 'Table'[31-60] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[SnapShot/Days Past Due]
                    = MAX ( 'Table'[SnapShot/Days Past Due] ) - 1
            )
        )
    ),
    "0.00%"
)

    Variance3 = 
FORMAT (
    DIVIDE (
        SUM ( 'Table'[61-90] ),
        CALCULATE (
            SUM ( 'Table'[61-90] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[SnapShot/Days Past Due]
                    = MAX ( 'Table'[SnapShot/Days Past Due] ) - 1
            )
        )
    ),
    "0.00%"
)

    Please note that you will need to create a measure for each column due to the visible limitations.

     

    3.Here's my final result, which I hope meets your requirements.

     

     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

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

martipe1's avatar
martipe1
Helper II
1 year ago

Thank you very much for your answer.

I'm unable to look at previous date.
My slicer is pointing to the new table

I just adjusted the measure for my table names

MEASURE =
VAR select1 =
    MAX ( 'time'[Snap Shot Date] )
RETURN
    IF (
        ISFILTERED ( 'time'[Snap Shot Date] ),
        IF (
            MAX ( 'Accounts_Receivable_SnapShot'[Snap Shot Date] ) = select1
                || MAX ( 'Accounts_Receivable_SnapShot'[Snap Shot Date] ) = select1 - 1,
            1,
            0
        ),
        BLANK ()
    )
I included the measure in the filters for the Matrix visual

And yet no previous date

What am I missing?

Thank you in advance for your comments.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, martipe1 

Thank you for your prompt response.

 

I apologise for not explaining clearly earlier. The new calculated table and the original table do not need to have a relationship established. If a relationship is established, the slicer will directly filter the original table.

If you still have questions, I recommend comparing with the PBIX file I previously shared. This should help with your understanding.

 

Additionally, please note that my solution is more suitable for date columns with continuous date data. If the dates are not continuous, I suggest inserting an index column as a contextual reference.


Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

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