Forum Discussion

rishirajdeb's avatar
rishirajdeb
Advocate I
5 years ago

Snapshot Requirement

Hi All,

 

I have checked previous posts related to this topic (the posts are mostly of generic nature), but haven't been able to find any positive response. So just thought of creating this post to check whether anything has changed lately, and also tried to describe the exact problem that I have in hand.

 

I need to create a snapshot table on the report for one of my fact tables.

Let me try to explain the requirement with some mocked up data.

Fact table (as of today, 16th of June 2021):

 

The requirement is to have a table/matrix created on the report (based on this fact table), in which everyday a record will get inserted with the day’s status.

For ex – today (16th of June 2021), as per the fact table – 2 tasks are ‘in-progress’ & 3 are ‘completed’.

Hence the output today should look like:

 

Tomorrow, if one ‘in-progress’ task gets completed (& no new task comes in), then tomorrow’s output should be:

 

(the date field on the fact table has basically no role to play in this)

Is there any way to do this in Power BI? (the data source is SharePoint – so can’t really implement it on the data source’s side in an efficient way)

 

Please stay safe!

 

Regards,

Rishi

 

GilbertQ collinq ibarrau amitchandak v-luwang-msft v-xuding-msft 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi rishirajdeb ,

     

    My sample data is this.

     

    In Power Query Editor, select the Status column and click Pivot, the conditions are set as follows.

     

    After clicking OK, you get the following table.

     

    Click Close&Apply, then create a calendar table by dax. Create the relationship between the calendar table and the main table.

     

    Table 2 = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

     

     

    Create the following table. Because you want to keep the table showing only the content of the day. You can create a measure, and then put it into Filters, set Show items when the value is 1.

     

    Measure = IF(MAX('Table'[Date])<=TODAY(),1)

     

     

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • rishirajdeb's avatar
      rishirajdeb
      Advocate I
      5 years ago

      Thanks for the post, however this is not the actual requirement! You are calculating the status counts based on the date field in your sample data - as I mentioned in the original post, the date in the fact table has nothing to do with it (there may not be a date field in the first place). The requirement is to calculate the counts each day - and save that as a snapshot.

      Looks like there is no efficient way to do this in Power BI as of now. I don't think incremental refresh is suitable for the use case I have. Maybe using power automate to create daily extract from Power BI & then using those to create a PBI table might help!