Snapshot data: Newsletter subscriptions per person (or newsletter) (or total) over time
Hello there. RE: https://michellepace.com/pics/snapshot.pbix Goodness how I struggle with DAX. Can someone please help me with a DAX measure to calculate the number of newsletter subscriptions? Below in the blue box, are all the details of my data. The pink shows the measure I have tried (but it only seems to work if I select ONE subscrip_id). Basically, if I am standing on a given date (eg 20 August) for a given subscription X, then I think the DAX needs to look back through time to the last row of data for subscription X (eg 15 August). If The subscription isOn for the 15th of August, then it is on and counted as a subscription for the 15, 16, 17, 18, 19, 20 August. But of course there is only one subscription count. That is the only way I can imagine solving this... but just how to take my words and put them in DAX is quite... well beyond me right now. Thanks very much in advance, Michelle
Finding Change in Start Date Between Snapshots
Hello, I've been trying to create a calculated column in my workbook that calculates the change in start date for each Activity ID since the last snapshot was taken. Snapshots are taken on the first of every month, and activities could move forward, backward, stay the same, be completed or cancelled, or be newly added in each snapshot. I can't figure out how to create this formula in DAX, so I was wondering if someone would be able to help me out? I've included a sample image of what I'm going for below with the highlighted column being the one I'm trying to create. I really appreciate any help anyone can offer!
Snapshot Fact with complex filters
Hi, I am having a situation similar to "Snapshot fact table" but with a bit of complexity. Please bear with me as I explain. I have a number of Customer IDs having unique Sale value. After the creation of any customer, At any given date, one customer sits in one stage. It can jump from one stage to another on any date. We have several stages. The table that I have just notes the date of jump, if any, for any customer id. So the new customers gets added(and they have a default initial stage called "B" ) and they can jump from one stage to another at any coming dates. What I want to create is a DAX formula which can sum up the sale values at any given date (the date might not be in the table below as it just notes the date of change of stages) for only specifc stages(for example stage- CC, IDP). So, if the user selects any date in reports, say he selects 20th Feb 2021, the dax sees what all customers have been in the table before and at this date, filters all the customers with stage - CC and IDP and sums up the sale values and give as a result. Please see table below: Cust Id Stage Date Sale Value ACC-00000016-SOS ST 8/11/2020 124293 ACC-00000016-SOS B 8/01/2020 124293 ACC-00000058-SOS DP 5/11/2020 194559.47 ACC-00000058-SOS NA 19/06/2020 194559.47 ACC-00000058-SOS IDP 3/12/2020 194559.47 ACC-00000058-SOS CB 16/12/2020 194559.47 ACC-00000058-SOS PP 13/07/2020 194559.47 ACC-00000058-SOS CB 19/06/2020 194559.47 ACC-00000058-SOS CB 12/06/2020 194559.47 ACC-00000058-SOS B 8/01/2020 194559.47 ACC-00000142-SOS CB 13/06/2020 42232.62 ACC-00000142-SOS CB 10/06/2020 42232.62 ACC-00000142-SOS CB 8/11/2020 42232.62 ACC-00000142-SOS NA 23/06/2020 42232.62 ACC-00000142-SOS CB 22/06/2020 42232.62 ACC-00000142-SOS NA 11/06/2020 42232.62 ACC-00000142-SOS NA 17/06/2020 42232.62 ACC-00000142-SOS B 8/01/2020 42232.62 Any help will be highly appreciated. Thanks, Rahul.Return earliest value of a boolean/text value based on snapshot dates
Hi guys, I've been banging my head against this challenge for a little over a week now, so I was hoping some of you smart people can help out! I have seen solutions relating to numerical values for snapshot comparisons that seem to work, but I can't figure out how to convert that when it's a boolean/string. So I've got a Snowflake schema in my data model view in Power BI. My company is also using daily snapshots of the data so we can follow the progress over time. This especially relates to our forecasts. We have a boolean field that denotes whether we expect to order to come through or not. When we sum up the expected orders with a snapshot date at the beginning of the month, this number will change throughout the month because orders are moved in and out of the expected field. To easily surface and investigate what those moved orders are, I would like to give the user a comparison period slicer to chose and show them the expected deals along with deals that were changed. In terms of the slicer (having a relative date slicer or two separate slicers) I'm open to suggestions. I've tried creating calculated columns showing the expected value at the first snapshot date (having a relative date slicer on snapshot date or two different slicers - one for a inactive relationship snapshot date dim and one for an active), but with no success because I can't get it to return the expected field in a CALCULATE function based on my conditions (USERELATIONSHIP to an inactive snapshot date dim, ALL(snapshot date dim) from primary relationship. I would really appreciate any help! Edit: I've added the data model and an example of the data and desired output This is my essentially the desired output based on a snapshot date filter between 1-4. Expected start would show what the expected value is at the start period selected regardless of what snapshot date we are looking at now. Snapshot Date Record Expected Expected Start 01-01-2020 aaaa TRUE TRUE 02-01-2020 aaaa TRUE TRUE 03-01-2020 aaaa FALSE TRUE 04-01-2020 aaaa FALSE TRUE 01-01-2020 bbbb FALSE FALSE 02-01-2020 bbbb TRUE FALSE 03-01-2020 bbbb TRUE FALSE 04-01-2020 bbbb TRUE FALSE Hopefully that's enough to go by otherwise I'm happy to add more!How to save snapshots of your data to generate trendlines on PBI Report Server
I would like to ask a question regarding snapshot data for Power BI On Prem. I am having a lot of requirements lately regarding looking back at previously loaded information (to generate trendlines based on quantity deltas) Is there a way to store the result of a SQL query, attach it a timestamp, and generate a table that contains all the snapshots? Thanks!Sales amount previous week (snapshot)
Hi, everyone I hope someone can help me I include the image to explain what I want to do. I would like to show how sales amount have been increased every month per week. To do that, I have tried to create a measure with this phormula. Sales amount last week = CALCULATE(SUM('Sales'[Sales amount]);FILTER(DimTable;DimTable[WeekCount]-1)) However, when I include the measure in the table, the result is the same than Sales Amount current week. Does anyone know how can I do that?. Thanks,
