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 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)

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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.

  • 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.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    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