Running tatal value from a date of first delivery to a specific stop date

[AmandaSoborg]

Hi everyone!

I need a Power BI Guru to help me 😞 I have tried to make this work all day, searching in the blogs and forums to find the answer, but nothing seems to work.

I have two different tables. A Date table and a data table. The Date table is called DimDate, and is made just like this; https://www.mssqltips.com/sqlservertip/4857/creating-a-date-dimension-table-in-power-bi/

My Data Table is from Salesforce, but the one I will show in this question is made in Excel. My Salesforce data looks like this;

Salesforce data

The Total Value runs over a period of 12 months. So, If my Total Sales is $1200, 1 month will be $100 and 1 day will be $3.28. The DimDate Table and the Salesforce Table is connected with a 1:1 relation between the date and the Date of First Delivery;

To show the running total, I am using the Quick Measures (preview);

Unfraternally Power BI shows the same value number if I am on the year level or the day level.

But of cause Power BI doesn’t know that the Total Value is over a 12M rolling period. Therefore I make a column where I divide the Total Value with 365 (to get the value over days from the start day). And I make a quick measure from this;

 

But I still have the same problem where Power BI shows the same value at every level.

pr. quarterpr. year

So what do I do? I want Power BI to know that one day is worth e.g. $3.28. But when my drill down level is on month, I want it to show $100 and so on.

Also; if possible, after the 12 months after the Date of First Delivery I want the Total Value to go from being an opportunity to an expected sale. So in basis, i want it to change colors after 12 months, but still continue to have the same value.

Please, please, please help me. I have tried to create different DAX measures and trying out a Running Total on those, but nothing is working.

[v-yulgu-msft]

Hi @AmandaSoborg,

From your sample data, I noticed that there existing multiple records per [Type] with different date. Do you need to sum up all [Total Value] first for each [Type], then, divide it with 12 to calculate sales per month? And, what is the usage of [Date of first delivery]?

Should we take it into account when calculating running total?

Regards,
Yuliana Gu

[AmandaSoborg]

Hi @v-yulgu-msft

Each Stage belongs to a different buyer. IT is like saying that you have many pants from different brands if you own a clothing store

The Date of First Delivery is very important. If for instance I have a DOFD on January 5th 2018 where the price is $3650, I will need the $3650 to run over a year. So the enddate will be on January 5th 2019, and each day should have the value of $10 (3650/365day=10). So from each DOFD the price should be devided by 365 and be running over a peiode of 365 days.

After the 365 days from the DOFD this sales is no longer counted as an opportunity. So the first year it is a opportunity, but thereafter I will need it to change colour, så that it will become a 'calculated sales' until the 31st of August 2021.

I hope this explains it, or else feel free to ask more questions