Wrong Subtotal

[awolf88]

Dear geniusses, 

I'm having an issue with the sub-totals once again. It seems even CHATGpt can't solve this, so I am praying that perhaps one of you can pin-point me in the right direction. 

Super simple Data-Setup with 3 Tables (Demo-Data Link available here: https://www.dropbox.com/scl/fo/i6oeb9mvrypo4bawjwov1/h?dl=0&rlkey=vgd213f2hh34sf6aki42s62l7

- Sales Table (Item/SalesDate/Revenue)

- OpenOrders TAble (Item/ShippingDate/Revenue)
- Budget Table (Month/Value)

All of them are linked via one-to-many relationships with a DimDate Table. 

Now I've created a few simple measures to demonstrate my issue: 

- YTD Sales = SUM(Sales[Revenue])

- OpenOrders = SUM('Open Orders'[Revenue])
- Budget 2023 = sum(Budget[Budget Value])
- Remaining Budget Months = // It is June 2023 now. Here i only want to display values of future months ahead (so from July-December 2023). This is working fine

Remaining Budget = 
VAR LastDateWithSales = 
    CALCULATE(
        MAX(Sales[Sales Date]),
        ALL( Sales)
    )
VAR Result = 
    CALCULATE(
        [Budget 2023],
        KEEPFILTERS('Calendar'[Date] > LastDateWithSales)
    )

RETURN
Result

// here I want to only show future months (anything > Present month)

- S1: YTD + Open Orders // this is my very basic Scenario 1 (S1) preview of all of 2023, adding YTD Sales and adding the Open Orders outlook for the remainder of the year

S1: YTD + Open Orders = 
[YTD Sales] + [Open Orders]

- S2: YTD + remaining Budget: and in this next part is where it doesn't add up for me: I want to display a Scenario where I take the YTD Sales from January-May, for the current month (June) I implement the MTD Sales + Open orders within June and from July onwards I would want it to refer to the "remaining Budget" column.  

S2: YTD + remaining Budget = 

VAR CurrentMonth = MONTH(TODAY())

RETURN
IF(
    MONTH(MAX('Calendar'[Date])) = CurrentMonth,
    [S1: YTD + Open Orders],
[YTD Sales] + [Remaining Budget]
)

So far so good. The values per Month seem to add up to where they should be, but the Grand total of S2 should not be €1.581 as it says. It should be €1.806. What seems to be missing in the total is the value of €225 from the Open Orders in June. 

Any idea why it is doing this and how I could fix it? 

Thank you so much for your help in advance. 

Best,

Alex

There is a filter on the page with Calendar[Year] = 2023 as I'm only interested on the current year. 

[awolf88]

Thank you so much, Miguel! Totally did the trick!

Best,

Alex

[MFelix · Accepted solution]

Hi @awolf88 ,

Believe what you can do is a new measure with the following code:

Measure = SUMX(DISTINCT('Calendar'[MonthName]), [S2: YTD + remaining Budget])