Running totals works fine only for the first month

[paliman]

Hello people.

I need some help calculating a running total based on a daily forecast.

So, here’s a screenshot of my model:

As Forecasts are calculated on a monthly basis, I can´t set a relation to the calendar table. So, my measures are as follows:

Total Forecast =

    CALCULATE(

        SUM(Forecast[Total Sales]),

        TREATAS(VALUES('Calendar'[Month & Year]),Forecast[Month & Year]))

Days in Context = COUNTROWS('Calendar')

Days in Month =

      CALCULATE(

             COUNTROWS('Calendar'),

                      ALL('Calendar'), VALUES('Calendar'[Month & Year]))

Daily Forecast =

      IF(HASONEVALUE('Calendar'[Date]) || HASONEVALUE('Calendar'[Month & Year]),

           DIVIDE([Days in Context],[Days in Month],0) * [Total Forecast],

           [Total Forecast])

So far so good, Then I want to calculate a running total based on my daily forecast:

Daily Forecast running total in Date =

     CALCULATE(

         [Daily Forecast],

         FILTER(

             ALLSELECTED('Calendar'[Date]),

             ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)))

And here's the result:

No matter what time frame, it always works fine ONLY for the first month and then adds up the whole monthly amount for each day

What am I missing?

Many thanks in advance

Pablo.

Sample file:

https://drive.google.com/file/d/1hug4Ll9ficUBjxYftuNt5406nwZnzD7u/view?usp=sharing

[v-xuding-msft]

Hi @paliman ,

If you haven't resolved it and you still need help, can you please upload the sample file into OneDrive for Bussinees and share again with us?  Now, I can't download it without access from your link.

[amitchandak]

@paliman , I think it would better if you create a daily forecast. and then use it.

Refer if this file :https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

Another option, do not split, check if that can work

https://www.youtube.com/watch?v=yPQ9UV37LOU

The reason it is not working, one measure is diving and distributing another one again aggregating that. Need to rework on the measures.

See any of the two approach can work.