New table from calculated measures whenever report is refreshed

[Mike_Mace]

Hi Forum happy pandemic days to all.

I am looking to create a new table that will save calculated measures (not information from other tables) whenever report is refreshed. 

The measures are count of rows of multiple other tables (and might have filters). The new table I am looking to create is a collection of the measurements I choose on those refresh days. Snip below.

Is there a dax function possible to create something like this.

Also, since this a new table, assume that the "new" dataset will be saved online on my workspace?

Any help/direction will be very much appreciated.

[v-kelly-msft]

Hi @Mike_Mace ,

You need to create a date slicer table as a dimension table,such as below:

Slicer = DISTINCT(UNION(VALUES(Table1[Date]),VALUES('Table2'[Date])))

Then create measures as below:

Measure 1 = CALCULATE(COUNT(Table1[Column1]),FILTER(ALL(Table1),'Table1'[Date]=SELECTEDVALUE(Slicer[Date])))

Measure2 = CALCULATE(COUNT(Table2[Value]),FILTER(ALL(Table2),'Table2'[Date]=SELECTEDVALUE(Slicer[Date])))

Remember to create relationships between dimension table and fact tables as below:

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Mike_Mace]

@v-kelly-msft thank you that is quite enlightening on creating slicer-tables.

Think I need to better explain the data my reports are giving me.

My data shows the status of varius items as it is (today) only.

In a week's time the status of all those items will be different plus new items submitted. So when i refresh Power BI in a week's time, the data from last week will be gone.

I can use the method you attached above to create the tables (albeit vertical on spreadsheet) highlighted in orange. This would be a table of the snapshop on that day.

My end goal is the table highlighted in green. I need to save each snapshop of the data whenever Power BI refreshes so I can create historic graphs. 

I am giving you snips as it seems I cannot attach the file

Including snips below

[v-kelly-msft]

Hi @Mike_Mace ,

I see,if it is the case,I would say sorry  as it is not supported in power bi,once the data is overwritten,we cant calculate the result before overwirtten,data will be overwritten and never be stored any more.

One way for you is to keep all the records then calculate the results as you want,once the data is never stored,you wouldnt calculate the result based on it.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Mike_Mace]

Thanks @v-kelly-msft 

Is it worth me exploring saving the snapshots of the data at specific dates on Azure or Sharepoint?

[v-kelly-msft]

Hi @Mike_Mace ，

Would it be troublesome?If not ,it may be a workaround!

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Mike_Mace , You can save measure but that will not take slicer values.

You can use summarize or summarizecolumns