Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Displaying historical data

Hello everyone.

 

I have very interesting topic and I can't figure out how to handle this task. I will be very grateful to hear the proposals of some experts as I'm a totally newbie in the PowerBI 😄

 

I have such sample data containing id, plant code, plant division, name, job_title, salary and the start_date when person started work. Crucial thing is that one person can be added to this audit table twice, like John (id=1, id=2) as his contract was updated on 12.01.2023.

idplantdivision  name   job_title      salary  start_date
1CTAutoJohnManager600012.01.2023
2CTAutoJohnManager300006.01.2023
3CTAutoMarcelWorker100016.01.2023
4GLAutoKenManager600018.01.2023
5GLAutoKenWorker200016.12.2022
6POBikesAndrewDirector1000014.01.2023
7POBikesAndrewManager700010.01.2023
8POAutoAndrewWorker100012.01.2023

 

The customer is interested in seeing the historical data. I set up a Date Slicer type Before on start_date column and just displaying and filtering the data in simple table works completely fine. The real problem begins when I want to aggregate the data.

 

Let's say the customer wants to view how many Managers were in the company on 15.01.2023. In this case the table will filter id=1, id=2 and id=7, but actually it should treat CT-Auto-John as a group and filter out just the latest entry in this group. I was trying to create the measure in which I GROUPBY plant, division and name, and aggregate by MAX(date), than create calculated table as a VAR in DAX, but in this case I couldn't access the calculated table columns to filter f.e. Managers and count the rows.

Do you have any idea how should I approach this task? Is it maybe possible to create a CalculatedTable that is constantly changing when user changes the date in the Slicer? Or maybe some DAX magic that I can't figure out on my own to GroupBy, select the latest entry and leave the other columns that are not present in the group or in aggregation (f.e. salary to than count the sum)?

 

I would be very grateful for your ideas and help.

Thanks in advance 🙂 

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    Anonymous,

     

    Try this solution.

     

    1. Create a calculated column in FactTable. Since the current row is unaffected by the date slicer, we can create a calculated column which identifies the current row (max date) for each combination of plant/division/name. This is more performant than a measure since it is calculated only once when the model refreshes, and it's reusable throughout the model.

     

    Current Row = 
VAR vPlant = FactTable[plant]
VAR vDivision = FactTable[division]
VAR vName = FactTable[name]
VAR vTable =
    FILTER (
        FactTable,
        FactTable[plant] = vPlant
            && FactTable[division] = vDivision
            && FactTable[name] = vName
    )
VAR vMaxDate =
    MAXX ( vTable, FactTable[start_date] )
VAR vResult =
    IF (
        FactTable[plant] = vPlant
            && FactTable[division] = vDivision
            && FactTable[name] = vName
            && FactTable[start_date] = vMaxDate,
        1
    )
RETURN
    vResult

     

     

    You can now use the filter "Current Row = 1" in measures and filters.

     

    2. Create a date table "DateSlicer" with no relationships. You can do this in Power Query or DAX.

     

    3. Create measure:

     

    Manager Count = 
VAR vDateSlicer =
    MAX ( DateSlicer[Date] )
VAR vResult =
    CALCULATE (
        COUNTROWS ( FactTable ),
        FactTable[start_date] <= vDateSlicer,
        FactTable[job_title] = "Manager",
        FactTable[Current Row] = 1
    )
RETURN
    vResult

     

    4. Create a slicer using DateSlicer[Date] and add the measure to a visual:

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    Anonymous,

     

    Try this solution.

     

    1. Create a calculated column in FactTable. Since the current row is unaffected by the date slicer, we can create a calculated column which identifies the current row (max date) for each combination of plant/division/name. This is more performant than a measure since it is calculated only once when the model refreshes, and it's reusable throughout the model.

     

    Current Row = 
VAR vPlant = FactTable[plant]
VAR vDivision = FactTable[division]
VAR vName = FactTable[name]
VAR vTable =
    FILTER (
        FactTable,
        FactTable[plant] = vPlant
            && FactTable[division] = vDivision
            && FactTable[name] = vName
    )
VAR vMaxDate =
    MAXX ( vTable, FactTable[start_date] )
VAR vResult =
    IF (
        FactTable[plant] = vPlant
            && FactTable[division] = vDivision
            && FactTable[name] = vName
            && FactTable[start_date] = vMaxDate,
        1
    )
RETURN
    vResult

     

     

    You can now use the filter "Current Row = 1" in measures and filters.

     

    2. Create a date table "DateSlicer" with no relationships. You can do this in Power Query or DAX.

     

    3. Create measure:

     

    Manager Count = 
VAR vDateSlicer =
    MAX ( DateSlicer[Date] )
VAR vResult =
    CALCULATE (
        COUNTROWS ( FactTable ),
        FactTable[start_date] <= vDateSlicer,
        FactTable[job_title] = "Manager",
        FactTable[Current Row] = 1
    )
RETURN
    vResult

     

    4. Create a slicer using DateSlicer[Date] and add the measure to a visual: