Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
hdhillon
Frequent Visitor

Take Monthly Snapshot of data. But only once a month

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) 

 

 

3 REPLIES 3
hdhillon
Frequent Visitor

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

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



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
bcdobbs
Super User
Super User

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.