Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculating Joiners & Leavers in Cumulative/Appended Report

Hi All,   I receive a report every week that consists of our current workforce and, each week, I append it to the previous week's report. My ultimate goal is to allow the user select a specific Rep...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    Anonymous 

     

    you can try this

     

    Column =
    var _next=maxx(FILTER('Table','Table'[Report Date Index]=EARLIER('Table'[Report Date Index])+1&&'Table'[Concat1]=EARLIER('Table'[Concat1])),'Table'[Concat1])
    return if('Table'[Concat1]="",0,if('Table'[Report Date Index]=max('Table'[Report Date Index]),0,if(_next<>"",0,1))
    )
     
    Column 2 =
    var _next=maxx(FILTER('Table','Table'[Report Date Index]=EARLIER('Table'[Report Date Index])+1&&'Table'[Concat2]=EARLIER('Table'[Concat2])),'Table'[Concat2])
    return if('Table'[Column]=0,0,if('Table'[Report Date Index]=max('Table'[Report Date Index]),0,if(_next<>"",0,1))
    )
     
    Column 3 =
    var _last=maxx(FILTER('Table','Table'[Report Date Index]=EARLIER('Table'[Report Date Index])-1&&'Table'[Concat1]=EARLIER('Table'[Concat1])),'Table'[Concat1])
    return if('Table'[Concat1]="",0,if('Table'[Report Date Index]=min('Table'[Report Date Index]),0,if(_last<>"",0,1))
    )
     
    Column 4 =
    var _last=maxx(FILTER('Table','Table'[Report Date Index]=EARLIER('Table'[Report Date Index])-1&&'Table'[Concat2]=EARLIER('Table'[Concat2])),'Table'[Concat2])
    return if('Table'[Column 3]=0,0,if('Table'[Report Date Index]=min('Table'[Report Date Index]),0,if(_last<>"",0,1))
    )
     
    pls see the attachment below

     

     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thank you!! This works exactly as expected, I'll mark it as solved. Thanks for the quick response, huge help!

ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

you are welcome

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    This is working exactly as I had hoped except for one thing that I'm hoping you can help with..

     

    Instead of having the user select the Report Date, I have them select the Index (which I display as "Week #"). When the user selects an Index, I actually want to give the Leaver and Leaver Elimination values for the selected Index minus 1. So, for example: if the user selects Index 2, I want to return the Joiner and Joiner Elimination values for 1/8/2025 (Index 2), and the Leaver and Leaver Elimination values for 1/1/2025 (Index 1).

     

    I assume this can be done with a slight adjustment to the formula, however I'm having trouble. Any help would be awesome!

     

    Thanks again!

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Ryan,

         

        Thanks for the reply. Unless I'm missing something, I don't see a difference in the dashboard you attached. Using your file, I put together a matrix (see below, I'm not able to attach a screenshot for some reason).

         

        Slicer filter: Index = 2

        Resource NameColumn 3 (Joiner)Column (Leaver)
        Adam00
        Alex00
        Gerard00
        Jared01
        Kristin00
        Rebecca00
        Stacy10
        Total11

         

        This is still showing Jared as a leaver in period 2 (which aligns with the table I provided in the original post), however I now want all of the leavers for the selected Index minus 1 (so in the case of Index 2 being selected, I want the leavers for Index 1 which would be Stacy).

         

        I hope this makes sense.. Please let me know if there's anything I can clarify, and thank you again for your help on this!