Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paliman
Frequent Visitor

Running totals works fine only for the first month

Hello people.

 

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

 

So, here’s a screenshot of my model:


Relationships.PNG

 

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:
Result.PNG
 
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:

 
2 REPLIES 2
v-xuding-msft
Community Support
Community Support

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.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.