Take Monthly Snapshot of data. But only once a month

[hdhillon]

All,

How do I build a table that contains a snapshot of data?  Which will do it the first time I refresh data for the month.  (If there is already a snapshot there, then it wont do it again)  I have a calendar table built into my model and thats linked upto my main data table

Lets assume its a Hospital Cases dataset.  I would like to record a snapshot of how many cases were open in the month.  (Before they get closed off.  Some cases get closed in a few days, others can take months) 

[hdhillon]

Thanks for the response,

I have one field that is called status and this changes from

• Open
• In Progress
• Closed

So if the status changes the field gets overwritten.  This is why I want to take a snapshot each month and then compare how many were Open and Closed each month in a year for example

[bcdobbs]

Do you have an open date and a closed date column?

[bcdobbs]

There's no direct way of doing it.

Tweaking the pattern as suggested in https://whatthefact.bi/power-bi/power-bi-datamart/persisting-temporary-accessible-data-via-power-bi-...

could be an option.

Alternatives are persisting it in a separate sql database or data lake.

Is there a reason you need to snapshot the data though? Can you not just have a live table of cases with a start and end date. You could then write a measure to calculate how many are closed each month.