Problems with accumulating in stacked column chart

[AmandaSoborg]

Hi everyone

I have a problem with my Power BI report

I want to calculate a running total of my sales, but i am not sure i am doing it right.

This is how my raw Data looks;

Raw data from excel (it is orginialy from SalesForce)

I have a DimDate table with all dates from 2000 to 2099. These two tabels are connected with a relation 1:*.

The total value is the value I will get over a periode of 12 months. Therefore i create a measure to calculate the value i will recive pr day. The measure looks like this;

Measure value pr day

Then i create another measure, to make the value pr day repete it self from date of first delivery to a specific enddate (31-12-2021). This measure looks like this;

I am not even sure that this is the right formular.

But when I plot the Valueadded into a visual with the date from DimDate i see this;

This is pr year, and if I drill down to the month or quarter i see this;

Value pr month

Value pr quarter

as you can see, the value is the same weather I look at it pr year, quarter or month. And then I also noticed that i have a dip in december 2017 when I look at months. Maybe the measure thinks it should average the value in stead of adding them together? and how do i get the visual to add the values from all four quarters into one year?

Please someone help me 🙂

[v-sihou-msft]

@AmandaSoborg

In this scenario, your "running" measure is based on current date to specify the "up to date" range. So when going to 2018, there's no fact data, then your running total supposes to keep same value all the way to the end. 

If you want to keep it in one year, you need to apply filter on month number like: 

=
CALCULATE (
    [measure],
    FILTER ( ALLSELECTED ( Table ), DimDate[MonthNum] <= MAX ( DimDate[MonthNum] ) )
)

If you want to calculate the running total within each year, you should use TOTALYTD() function. 

Regards,

[AmandaSoborg]

@v-sihou-msft

My problem is not limiting the dates to show me a year or a time line

My problem is, that when I look at my data pr year or pr month, the value is the same. Please look at the pictures.This is my Value pr month

And this is my value pr quarter.

As you can see, the value is the same on the y-axis no matter how far I drill down.

Each one of my values has a start date (Date of first delivery). If I have a start date on the 1/1-2018, I want the value to show from that day until I tell it to stop. So lets say I have $100 pr day for this specific product. When I am on the day level, I want it to show me the $100 for each day. But if I drill up to the month level, I want the y-axis to display 3100 (31*100=3100). When if i drill up again, so I am on the Quarter level, I want the value to be 9000 (90*100=9000).

But right now, the value on the y-axis doesn't change when I drill through the different levels.

So how do I do that?

[Phil_Seamark]

Hi @AmandaSoborg

Do you want to reset the running total each year?

[AmandaSoborg]

Hi @Phil_Seamark

I want the visual to show me the value of the opportunities from the Date of First Delivery and until 2021. So if I have an opportunity with a DOFD on the 1st of january 2018 I want this to continue until the 31st of december 2021. Then i have another opprtunity starting the 31st of january 2018. This opportunity should then be added to the first opportunity on this date.

I want the visual to show the calculated value pr day from DOFD to the 31st of December 2021. This also means, that I want the visual so summarize the data when i move up a level. E.g. If I have a value of $100 pr day, in the month of January and I look only at the month of January, I want to see the value of $100 for each day. But if I move up a level and to where I can see all of my months of a year, and not just the days, I want the visual to display $3100 on the month of January ($100*31days=$3100) 

Right now I do not wish the data to reset each year 🙂