Snapshot Data

[mmills2018]

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 capturing the last month of data (assuming its because of my LASTDATE), any ideas how i can capture this for all of 2021?

VAR LastDateSnap = LASTDATE('Snapshots'[Data As Of]) 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]=LastDateSnap))+0,0)

[v-henryk-mstf]

Hi @mmills2018 ,

Has the problem been resolved? If not, please provide test data (delete sensitive information) I will answer you as soon as possible.

Best Regards,
Henry

[v-henryk-mstf]

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.

[lbendlin]

If you have a calendar table in your data model then you can modify the filter context to ignore all date filters except the fiscal year.

I recently stumbled onto a quirky alternative.  PARALLELPERIOD([date],0,Year) .  Yes, I already said quirky.  But it works.