Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Duplicating missing dates data with the previous available date data.

Hi All,

 

I am new to Power BI. I have a scenario where i have got missing rows for some days in the given Table when ETL job gets failed and data are not being incremented in the DWH Table. Like below data:

 

SNAPSHOT_DATEEMP_IDEMPLOYEE_GROUP_DESCRSITECOUNTRYSTATECITYTEAM_NAME
20/01/20241AXYINMaharashtraPuneTiger
20/01/20242BXYUSOregonWilTiger
20/01/20243APQINBiharMfpLion
22/01/20241AXYINMaharashtraPuneTiger
22/01/20242BXYUSOregonWilTiger
22/01/20243APQINBiharMfpLion
22/01/20244AXYDEHesseFrankfurtLion
25/01/20241AXYINMaharashtraPuneTiger
25/01/20242BXYUSOregonWilTiger
25/01/20243APQINBiharMfpLion
25/01/20244AXYDEHesseFrankfurtLion
25/01/20245BPQDEBavariaMunichTiger
27/01/20241AXYINMaharashtraPuneTiger
27/01/20242BXYUSOregonWilTiger
27/01/20243APQINBiharMfpLion
27/01/20244AXYDEHesseFrankfurtLion
26/01/20245BPQDEBavariaMunichTiger

 
As you can see that there are 4 dates i.e. 21/01/2024, 23/01,2024, 24/01/2024, 26/01/2024 for which job failed. So i just want to duplicate those missing dates data with the previous availble date data like below:

 

SNAPSHOT_DATEEMP_IDEMPLOYEE_GROUP_DESCRSITECOUNTRYSTATECITYTEAM_NAME
20/01/20241AXYINMaharashtraPuneTiger
20/01/20242BPQUSOregonWilTiger
20/01/20243AKLINBiharMfpLion
21/01/20241AXYINMaharashtraPuneTiger
21/01/20242BPQUSOregonWilTiger
21/01/20243AKLINBiharMfpLion
22/01/20241AXYINMaharashtraPuneTiger
22/01/20242BPQUSOregonWilTiger
22/01/20243AKLINBiharMfpLion
22/01/20244AMNDEHesseFrankfurtLion
23/01/20241AXYINMaharashtraPuneTiger
23/01/20242BPQUSOregonWilTiger
23/01/20243AKLINBiharMfpLion
23/01/20244AMNDEHesseFrankfurtLion
24/01/20241AXYINMaharashtraPuneTiger
24/01/20242BPQUSOregonWilTiger
24/01/20243AKLINBiharMfpLion
24/01/20244AMNDEHesseFrankfurtLion
25/01/20241AXYINMaharashtraPuneTiger
25/01/20242BPQUSOregonWilTiger
25/01/20243AKLINBiharMfpLion
25/01/20244AMNDEHesseFrankfurtLion
25/01/20245BMNDEBavariaMunichTiger
26/01/20241AXYINMaharashtraPuneTiger
26/01/20242BPQUSOregonWilTiger
26/01/20243AKLINBiharMfpLion
26/01/20244AMNDEHesseFrankfurtLion
26/01/20245BMNDEBavariaMunichTiger
27/01/20241AXYINMaharashtraPuneTiger
27/01/20242BPQUSOregonWilTiger
27/01/20243AKLINBiharMfpLion
27/01/20244AMNDEHesseFrankfurtLion
27/01/20245BMNDEBavariaMunichTiger

 

Thank you very much for your response.

Cheers,

Sundar

Need Help
Tips and Tricks

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I would like to show this in line chart about how headcount for each "team" and "employee group descr" are progressing in the time-period.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        I recommend you use a disconnected calendar table and measures.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi lbendlin ,

     

    I would like to group by the data w.r.t. Snapshot_date, Employee_Group_Descr, and Team_Name and count the number of employees. I would like to use the Country, State and City as the filters to the visualization.

     

    Please let me know in case you have some more questions.

     

    Cheers,

    Sundar

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Assuming the first table is the only input table we have, could you show the final expected result in a simple Table format?