Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Monthly Budget to Daily

Hi Everyone,   I need to compare my cumulative sales to cumulative budget sales and for the visual I would like to share, I need to have daily granularity of the budget sales.  I have done a lot of...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, accumulation usually has two important factors: time and addition. 

    There are two formulas that are more common:
    CALCULATE( SUM(), FILTER( ALL(), [Date]<=MAX([Date])))

    Or

    SUMX( FILTER( ALL(), [Date]<=MAX([Date])),[ ])

    For the first formula, it only accept a column in the SUM function.

     

    In your formula, there is no step of addition, here's my solution:

    Cumul. Bdgt Canada (USD) =
SUMX (
    FILTER (
        ALLSELECTED ( CalendarDate ),
        CalendarDate[Date] <= MAX ( CalendarDate[Date] )
    ),
    [Daily Bdgt Alloc (USD)]
)

    Get the correct result in my sample.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Everyone,

 

I was able to get the daily amount to work, but now I am struggling with the cumulative total.

 

I can get the cumulative total of sales using DAX, but when I use DAX for the cumulative budget I get a step function.  The DAX I used is,

 

Cumul. Bdgt Canada (USD) =
calculate([Daily Bdgt Alloc (USD)],
Filter(allselected(CalendarDate),
CalendarDate[Date]<=max(CalendarDate[Date])))
 
And the result cumulative visual I get looks like the below,
 

 

So it is clearly not accumulating.  

 

I have cumulation measures throughout my report that work with the DAX above, but it does not seem to work in this case.

 

Any help is appreciated.

 

Rich

 
 

 

 

 

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
4 years ago

Hi Anonymous ,

According to your description, accumulation usually has two important factors: time and addition. 

There are two formulas that are more common:
CALCULATE( SUM(), FILTER( ALL(), [Date]<=MAX([Date])))

Or

SUMX( FILTER( ALL(), [Date]<=MAX([Date])),[ ])

For the first formula, it only accept a column in the SUM function.

 

In your formula, there is no step of addition, here's my solution:

Cumul. Bdgt Canada (USD) =
SUMX (
    FILTER (
        ALLSELECTED ( CalendarDate ),
        CalendarDate[Date] <= MAX ( CalendarDate[Date] )
    ),
    [Daily Bdgt Alloc (USD)]
)

Get the correct result in my sample.

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • ngocrin1's avatar
    ngocrin1
    Frequent Visitor
    3 years ago

    Tks. It also help me a lot