Forum Discussion

mmills2018's avatar
mmills2018
Helper IV
5 years ago

Snapshot Data

Hello,   I want to capture anyone who was coded to "Professional - P3", "Professional - P4" and "Professional - P5" at any time in 2021.  I currently have the below calculation , but its only captu...
Need Help
v-henryk-mstf's avatar
v-henryk-mstf
Community Support
5 years ago

Hi mmills2018 ,

 

According to your description, it may be due to the wrong filtering context. Create a calendar table without establishing a relationship. The created parameter selects the date from the calendar table. The reference is as follows:

1 =
VAR a =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    IFERROR (
        TOTALYTD (
            (
                CALCULATE (
                    DISTINCTCOUNTNOBLANK ( 'Job Changes'[Associate ID] ),
                    Snapshots[TalentPool] <> BLANK (),
                    'Job Changes'[Promoted to M1 - M3] = "Yes"
                )
            ),
            'Job Changes'[Effective Date]
        )
            / (
                CALCULATE (
                    DISTINCTCOUNTNOBLANK ( 'Snapshots'[AssociateID] ),
                    Snapshots[TalentPool] <> BLANK (),
                    'Snapshots'[TerminationDate] = BLANK (),
                    'Snapshots'[ManagementLevel] = "Professional - P3"
                        || 'Snapshots'[ManagementLevel] = "Professional - P4"
                        || 'Snapshots'[ManagementLevel] = "Professional - P5",
                    'Snapshots'[Data As Of] = a
                )
            ) + 0,
        0
    )

 

If the problem is still not resolved, can you provide a test data model (delete sensitive information), otherwise I cannot do further tests based on the formula.


Best Regards,
Henry


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