Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Only show data when date X is less than date Y

In my database, I have two tables:


Table1 holds information about employees' hours worked, which are registered every friday. 

Table2 holds information about employees termination date.

 

In PowerBI, I have a table that shows 'Week of the Year', 'Name' and 'Hours' registered. Week of the Year grabs week-number from a yearly calendar. 

Brian's employment was terminated 18.01.2019 which is in week 3. I do not want any data from Brian in week 4 to be shown in the table. 

 

I tried to replace the Week-column with a DAX column:

Week of the Year DAX = IF(FIRSTNONBLANK(Table2[TerminationDate], 1) <> BLANK() && FIRSTNONBLANK(Table1[Week], 1) < FIRSTNONBLANK(DimWorker[EmploymentEndDate.Week], 1), BLANK(), DimDate[Week])
 
No errors, but doesn't do much either. Am I close?
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous ,

    You can use following calculate table formula to create a table with filtered summary records:

    Week of year =
VAR filtered =
    FILTER (
        Table1,
        [Period]
            <= MAXX (
                FILTER ( Table2, Table2[Name] = EARLIER ( Table1[Name] ) ),
                [Termination date]
            )
    )
RETURN
    SUMMARIZE (
        ADDCOLUMNS ( filtered, "Week", WEEKNUM ( [Period], 1 ) ),
        [Week],
        [Name],
        [Hours]
    )

    Regards,

    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

    You can use following calculate table formula to create a table with filtered summary records:

    Week of year =
VAR filtered =
    FILTER (
        Table1,
        [Period]
            <= MAXX (
                FILTER ( Table2, Table2[Name] = EARLIER ( Table1[Name] ) ),
                [Termination date]
            )
    )
RETURN
    SUMMARIZE (
        ADDCOLUMNS ( filtered, "Week", WEEKNUM ( [Period], 1 ) ),
        [Week],
        [Name],
        [Hours]
    )

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I marked your solution as it did solve the specific example I provided.

       

      Is there an alternative option where you do not create a calculated table? My real tables have measures in them, and slicers needs to cooperate with the table. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous ,

        Unfortunately, current power bi does not support to create a dynamic calculate column/table based on slicer/filter. The measures can be dynamic change by filter/slicer, but its result will be fixed and not effect by filter/slicer if you used in calculate column/table.

        Regards,

        Xiaoxin Sheng