How to create historic monthly snapshot summary of data within PowerBI

[ysf]

Below is an example of what I would like to have scheduled to run on a monthly basis. 

SELECT Monthkey ,Division ,Segment ,count() as 'Number of employees' 

  FROM HR.dbo.HR_DAILY

Group by   Monthkey, Division, Segment

See example below that would reflect after running in the second month:

Monthkey	Division	Segment	Number of Employees
202105	A	AA	233
202105	B	BA	51
202105	C	CA	23
202105	D	DA	33
202105	A	AB	40
202105	C	CB	311
202106	A	AA	222
202106	B	BA	57
202106	C	CA	33
202106	D	DA	21
202106	A	AB	39
202106	C	CB	298

How can we have this scheduled to run in PowerBI instead of a SQL job that runs on a monthly basis as we do not have permissions on the database to perform this function and merely access to a view of the data. 

[lbendlin]

Do you have control over the data source? Then do your snapshots there (bonus points if that source is event based like SFDC). Otherwise maintain your own storage in SQL server, or CSV files, or whatever you have available.

[ysf]

Hi 

Thanks @lbendlin 

I thought the same thing but didn't want to rule it out.

Can you advise which other way can I have the data to append so that I can finally show trend comparison through time.

Regards

[lbendlin]

That's not a snapshot, that is a grouping query.  It should work as you stated it, and grab the updated source data when you refresh the Power BI dataset.

Power BI has no memory. You cannot do snapshots with Power BI. Snapshots need to be done outside of Power BI.