Cumulative budget per day

[Anonymous]

Hello,

I'm struggling with getting a cumulative budget per day. The budget is fixed data coming from an Excel file. It's broken up into a few categories:

Budget per month, per sales reps, per product category.

I'm able to create  a matrix visual and have the current sales next to the Monthly Budget and have it filter by Month, Sales Rep and Product.  Below is an example of what I currently have. I was able to verfy all the data is correct.

The sales group would like a cumulative per day as well. I'm not sure if I have an incorrect relationship behind the scenes, bad date table or possibly a missing date column in my budget table to create the correct relationship? The results I get right now show incorrect numbers that don't validate against the budget. I inherited a crazy pbix file and I have a feeling the budget isn't being spread across the correct date table. I can share the file if need be. Any help or guidance is appreciated.

[amitchandak]

You are not showing data by day. What Cummaltive means here.

[Anonymous]

You’re right. In the example picture I’m just showing that I can show it by month (if you choose a month from the slicer) or by year if you you don’t choose a month from the slicer.
I’m trying to get it to show cumulatively per day.

[amitchandak]

Datesmtd/totalmtd and datesytd/totalytd can help you. you need to have a date table.

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date]))

You can use isfiltered or isinscope, to control MTD and YTD.

https://docs.microsoft.com/en-us/dax/isinscope-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[danextian]

Hi @Anonymous,

When does your budget reset - monthly, quarterly, all time cumulative, etc ?

[danextian]

Hi @Anonymous ,

Try this:

Cumulative =
VAR __MTD =
    TOTALMTD ( [Total], 'Table'[Date] )
VAR __YTD =
    CALCULATE (
        [Total],
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Year] ),
            'Table'[Date] <= MAX ( 'Table'[Date] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( 'Table'[Year] ), __YTD,
        HASONEVALUE ( 'Table'[Month] ), __MTD
    )

[Anonymous]

I assume it would reset every month. But as you can see, they want a slicer for the month. So if no month is chosen I assume it should show MTD and just be cumulative almost like a YTD. And if a month is chosen from the slicer it would show MTD.